Slicer from 3 different tables

Slicer from 3 different tablesFollow
Prem Guru
Prem GuruNov 04, 2022 10:39 AM
Hello, See attached for your reference. I had added 3 different table from excel. All the tables does …
1. Slicer from 3 different tables

Bronze Contributor
Prem Guru
Posted Nov 04, 2022 10:39 AM | view attached
Hello,

See attached for your reference.

I had added 3 different table from excel. All the tables does not include same level/no of columns.

But there are few columns into this 3 tables which are similar.

Have added a Date table and a relationship is created with Hire Date column.

I need a single slicer which will be “Business Unit”.

Tried creating a table using Distinct Value to get from “Business Unit” column. But this is not working up to mark.

Reason being is because – into Business column we do not have similar names into all the 3 source / data tables.

For e.g. in Table 1 – Business column we have “Manufacturing” but the same cannot be in Table 2 or 3.

Please advise with your expertise.

——————————
Prem Guru
——————————
Attachment(s)

pbixSlicers.pbix

2. RE: Slicer from 3 different tables

Kaz Shakir
Posted Nov 04, 2022 07:19 PM | view attached
@Prem Guru,
You can create a calculated table that combines the values of the business unit field from each of your tables. The formula would look like this:
BusinessUnits =
DISTINCT(
UNION(
VALUES(Table1[Business Unit]),
VALUES(‘Table1 (2)'[Business Unit]),
VALUES(‘Table1 (3)'[Business Unit])
)
)​
​Then, in your data model you can connect this new calculated table to your existing three tables. Just remember that you need to change the relationship between your three tables and the Calendar table so that they are single direction, and not both. Your final data model should look like this:

Then, in your visualization, simply use the Business Unit column from the BusinessUnits table that you just created:

Then you should be able to filter by business unit.
I’m attaching my .pbix with those changes for you to review.

Please let us know if that achieves the result you wanted.

Kaz.

——————————
Kaz Shakir
Sr. Program Manager, Asset Planning
TN
——————————

Attachment(s)

pbixSlicers_r2.pbix