Forum Discussion
Filter two tables based on User input and create combined tables to use in Measure calculation
Hi,
I am trying to solve a business problem in PowerBI. I have two data sets that contain forecast details of two different models. The two datasets may have some row items common between them. I have described the two data tables below:
Dataset1:
Forecast Method | Plant | Cost Center | Production Quantity | Spend | Price per unit |
Model 1 | P1 | A | 100 | 250 | 15 |
Model 1 | P1 | B | 200 | 150 | 19 |
Model 1 | P1 | C | 300 | 350 | 25 |
Model 1 | P2 | P | 200 | 150 | 19 |
Dataset 2:
Forecast Method | Plant | Cost Center | Production Quantity | Spend | Price per unit |
Model 2 | P1 | A | 100 | 250 | 15 |
Model 2 | P1 | B | 200 | 150 | 19 |
Model 2 | P2 | P | 200 | 150 | 19 |
There is a 1:1 relationship between the two tables using the column “Cost Center”.
Problem:
I want to create two pages in my dashboard. in first page, there needs to be filter for Cost center from data source 2. It will enable the user to select multiple cost center at once. Based on that selection, The first dataset set should get filtered by excluding CC centers selected by the user and Second table should get filters only for the selected cost centers and used the combined output of the both the tables in a single visual table in second page.
Suppose the user selects cost centers: A and P, then the visual table should look like this:
Forecast Method | Plant | Cost Center | Production Quantity | Spend | Price per unit |
Model 1 | P1 | B | 200 | 150 | 19 |
Model 1 | P1 | C | 300 | 350 | 25 |
Model 1 | P2 | Q | 300 | 350 | 25 |
Model 1 | P2 | R | 100 | 250 | 15 |
Model 2 | P1 | A | 100 | 250 | 15 |
Model 2 | P2 | P | 200 | 150 | 19 |
Please help me if at all, how can it be solved in PowerBI. Also, is it possible to solve it through Powerapps?
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
6 Replies
- amitchandak
Super User
Anonymous , Not sure I got it. But you can merge two tables
https://radacad.com/append-vs-merge-in-power-bi-and-power-query
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 ShengAnonymousthere 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 )
