Forum Discussion
Filter two tables based on User input and create combined tables to use in Measure calculation
HI Anonymous,
In fact, it not means append these tables to one on the query editor side.
For the detailed operations, you can refer to the below steps.
1. Add a calculated field to your tables combine Forecast and Cost Center fields.Merged = Table[Forecast] & "/" & Table[Cost Center]2. Create a calculate table based on two table records.
Combine Table = UNION ( Table1, Table2 )3. Extract step1 Merged field from two tables and use them to create a calculated table as the bridge.
Bridge = DISTINCT ( UNION ( ALL ( Table1[Merged] ), ALL ( Table2[Merged] ) ) )4. Use the Merge field as a relationship key to creating relationships between the bridge and your tables.
After these steps, you can use the 'Combine Table' field as source of the slicer to filter visuals with table1 and table2 fields.
Regards,
Xiaoxin Sheng
HI Anonymous,
You can merge two tables and extract the 'forecast method' and 'cost center' as a relationship key to link merge table ad raw tables.
Relationship in Power BI with Multiple Columns
After these steps, you can simply use filters to filter on the visual with merge table records.
Regards,
Xiaoxin Sheng
Hi Anonymous. Thanks for your reply. Do you mean "append" these two tables and create extract Costcenter and Forecast method as raw tables and appended tables?
Anonymousthere is a caveat here. The Cost centers in table 2 are very less as compared to the First table approx 100:1 ratio in terms of the number of CC in both the tables.
So, It's required to provide filter only from the second table (model 2) because by default it's expected to use data from the first table for all the following measures and calculations. But, if a user selects anything from the second table filters, The measures should use combination of data sets in the calculation like below:
Basically, all of my measures should work on the below data selection. But i don't know how to achieve it.
(All the cost centers from first table) - (Selection Cost Center data from the First table) + (Selected COst center data from the second Table )
HI Anonymous,
In fact, it not means append these tables to one on the query editor side.
For the detailed operations, you can refer to the below steps.
1. Add a calculated field to your tables combine Forecast and Cost Center fields.Merged = Table[Forecast] & "/" & Table[Cost Center]2. Create a calculate table based on two table records.
Combine Table = UNION ( Table1, Table2 )3. Extract step1 Merged field from two tables and use them to create a calculated table as the bridge.
Bridge = DISTINCT ( UNION ( ALL ( Table1[Merged] ), ALL ( Table2[Merged] ) ) )4. Use the Merge field as a relationship key to creating relationships between the bridge and your tables.
After these steps, you can use the 'Combine Table' field as source of the slicer to filter visuals with table1 and table2 fields.
Regards,
Xiaoxin Sheng