Forum Discussion
Microsoft EmployeeHow to slice data in different charts from different tables based on same slicer ?
Hi rumittal,
you require a set of dimensions and a star schema. as an example you can create a 'scenario owner' dimension either at the data source with a view that gets the distinct values from both of your fact tables, with a dax calculated table
Scenario Owner = DISTINCT(UNION(VALUES(consistency_across[scenarioowner]), VALUES('query1'[scenarioowner])))or within power query. Once you have the dimension create a 1->* relationship from the dimension to both fact tables, then add the dimension attribute as your slicer or filter element.
Voila a single filter to control them all!
Resident RockstarHi rumittal,
you require a set of dimensions and a star schema. as an example you can create a 'scenario owner' dimension either at the data source with a view that gets the distinct values from both of your fact tables, with a dax calculated table
Scenario Owner = DISTINCT(UNION(VALUES(consistency_across[scenarioowner]), VALUES('query1'[scenarioowner])))or within power query. Once you have the dimension create a 1->* relationship from the dimension to both fact tables, then add the dimension attribute as your slicer or filter element.
Voila a single filter to control them all!
Microsoft EmployeeThanks for the help, it worked !
In case someone doesnt know Power Query M language, they can do below to create a dimension table (got it from powerbi learning PDF)
To create a relationship in this case, we need to create a logical dataset of all the CustomerNames across the two datasets. In the Query tab, you can use the following sequence to create the logical dataset:
- Duplicate both queries, naming the first Temp and the second CustomerNames.
- In each query, remove all columns except the CustomerName column
- In each query, use Remove Duplicate.
- In the CustomerNames query, select the Append option in the ribbon, select the query Temp. 5. In the CustomerNames query, select Remove Duplicates. Now you have a dimension table that you can use to relate to CustomerIncidents and WorkItems that contains all the values of each.
Now use Dimension Table : CustomerNames, to create slicer . If power BI doesn’t create relationship by itself, go to Manage Relationship>Edit >create 1:* relationship from dimension to fact tables.