When creating a new Power BI report based on your data source, you might find yourself in a situation where you want to show values from two separate tables from your data source on the same visualization. Let’s look at the following example.
Let’s assume that you have two tables:
1. A “Sick Time” table which contains information on hours when employees were sick and missed work:
2. A “Weekend” table which contains information on hours when employees worked on the weekend.
Once you import these two tables into Power BI, you can display the data in one custom visual in a couple of different ways. Let’s choose Custom Calendar by Akvelon for that purpose, and begin by supplying the following field buckets of the visual:
You can drop both metrics here, but how would you choose a Date column if you have two separate Date columns on two separate tables? We can solve this problem in two different ways.
Choose to create a new table that contains the information from both tables
The first option is to use the “Append Queries as New” command of Query Editor:
or directly create a new table using Table.Combine M command:
= Table.Combine({Sick, Weekend})
After doing that you will have a new table which is a combination of two source tables:
Create a snowflake scheme with a dictionary Date table
Another way to solve this problem is to create a snowflake scheme with a Dictionary Date table to contain distinct dates from both tables. You can use the same approach for other types of categories, and when you have three or more tables.
Create a new calculated table:
Distinct Date = DISTINCT (UNION (SELECTCOLUMNS (Sick,"Date",Sick[Date]),SELECTCOLUMNS (Weekend,"Date",Weekend[Date])))
Now you can create the new “Distinct Date” with a “Date” column that contains unique dates from both the “Sick Time” and “Weekend” tables. SELECTCOLUMNS DAX function returns a new table with the new column containing values from the 3rd parameter (scalar expression). The new table will look like the following:
Now you can use the Power BI Desktop features to set up relationships and create a snowflake schema:
Following the snowflake schema creation, you can now drop the “Date” column from the “Distinct Date” table and the two metrics from the “Sick Time” and “Weekend” tables to Custom Calendar by Akvelon custom visual:
Now you have a visualization that contains information from 2 separate tables. When the underlying data of those 2 tables are changed, the visual will also be updated accordingly.
