Filter two tables based on User input and create combined tables to use in Measure calculation

[Anonymous]

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?

[v-shex-msft]

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

[Anonymous]

Hi @v-shex-msft. Thanks for your reply. Do you mean "append" these two tables and create extract Costcenter and Forecast method as raw tables and appended tables? 

[Anonymous]

@v-shex-msftthere 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 )

[v-shex-msft · Accepted solution]

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

[amitchandak]

@Anonymous , Not sure I got it. But you can merge two tables

https://radacad.com/append-vs-merge-in-power-bi-and-power-query

[Anonymous]

Hi, i need it to work dynamically using a page slicer. The Append/ merge functionally will work in Power Query not on Report report page.