Akvelon | How to Show Values from Two Tables on One Power BI Visualization
A common hang up when trying to visualize data in Power BI comes when taking values from two separate tables. Here's how to solve this problem with ease.
Two tables Power BI Visualizaton
19461
post-template-default,single,single-post,postid-19461,single-format-standard,ajax_fade,page_not_loaded,,qode-theme-ver-8.0,wpb-js-composer js-comp-ver-4.9.2,vc_responsive
 

17 Oct How to Show Values from Two Tables on One Power BI Visualization

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:

sick table

2. A “Weekend” table which contains information on hours when employees worked on the weekend.

weekend table

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:

drag and drop

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:

append queries as new

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:

two tables in one table for Power BI

Create a snowflake scheme with 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:

date column

Now you can use the Power BI Desktop features to set up relationships and create a snowflake schema:

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:

two values displayed on Custom Calendar

Now you have visualization that contains information from 2 separate tables. When the underlying data of those 2 tables are changed, the visual will also will be updated accordingly.

get the Custom Calendar

No Comments

Sorry, the comment form is closed at this time.

Akvelon

Akvelon