Filter two tables based on one Page slice and retain it for future calculations

[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. On first page, I want to give a filter from table to select some cost centers. Based on that selection, I want to filter the data source 1 to exclude the selected cost centers but data source 2 should include the selected cost centers.

In the Second page there be a filter of Plant for the users and all the graphs and Calculation should use the combined tables containing both the filtered data set as described earlier and based on the Plant selection the CC selection should still retained as per the subset at that Plant level.

Suppose the user selects cost centers: A and P,

then the visuals should work on the 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?

I am open to connect via skype or Emails for more details and explanations by screen share.

[MFelix · Accepted solution]

Hi @Anonymous ,

There is one aspect of your setup that I don't understand but maybe is just a visual thing, you want a single table on the first page or two tables?

I have create the followin setup:

• Cost Center table  - to use as slicer
• Two measures:

Table 1 Filtering =
VAR selected_costcenter =
    ALLSELECTED ( CostCenter[Cost Center] )
RETURN
    IF ( NOT ( MAX ( Table1[Cost Center] ) IN selected_costcenter ); 1; 0 )

Table 2 Filtering =
VAR selected_costcenter =
    ALLSELECTED ( CostCenter[Cost Center] )
RETURN
    IF ( MAX ( Table1[Cost Center] ) IN selected_costcenter; 1; 0 )

Now if you use this two measure and filtered them on 1 you should get expected result.

See attach PBIX.

[Anonymous · Accepted solution]

Hi @Anonymous 

As MFelix replied before，you may use a slicer and create measures to achieve your purpose.

On First Page:

Click New table in Modeling Tab:

Filter = VALUES('Table'[Cost Center])

Now, you have created a table named Filter which use value(Cost Center) in data source1.

And make a slicer by this table, result as below:

1.   To filter the data source 1 to exclude the selected cost centers:

Measure1 is as below:

Measure1 =

var a = ALLSELECTED('Filter'[Cost Center])

return

CALCULATE(MAX('Table'[Cost Center]),FILTER('Table',NOT('Table'[Cost Center] in a)))

2.   To filter the data source 2 to include the selected cost centers:

Measure2 is as below:

Measure 2 =

var _b = ALLSELECTED('Filter'[Cost Center])

return

CALCULATE(MAX('Table'[Cost Center]),FILTER('Table','Table'[Cost Center] in _b))

Then we add Measure1 into the table visual made by Data Source1, and add Measure2 into the table visual made by Data Source2.

If we select A and B in Slicer, the result is as below:

Note1: You can create a measure by click New Measure in Home Tab.

Note2: You can make multiple choice in Slicer by Ctrl+ left click.

On Second Page:

You can make a Slicer like before or use Sync Slicers. More info: https://docs.microsoft.com/en-us/power-bi/visuals/power-bi-visualization-slicers

To get the visual like you show me, we need to combine the table. I have some method:

1.   Use Append in Power Query Editor:

Click Transform data in Home Tab:

Click Append Queries and select Append Queries as new:

Select Table (DataSource1) As Primary Table:

Then we get Result:

2.   Click New Table in Home Tab:

Table 3 = UNION('Table','Table 2')

Result:

Due to the table we built is static and cannot be dynamically selected according to the slicer. Therefore, we still need to create a new measure (Measure3)to display.

Measure 3 =

var _c = ALLSELECTED('Filter'[Cost Center])

return

IF(

MAX('Append1'[Forcast Method]) = "Model1",

CALCULATE(MAX('Append1'[Cost Center]),FILTER('Append1',NOT('Append1'[Cost Center] in _c))),

CALCULATE(MAX('Append1'[Cost Center]),FILTER('Append1','Append1'[Cost Center] in _c))

)

Then if we select A and P, Result is as below:

You can download the pbix file form this link:

https://qiuyunus-my.sharepoint.com/:u:/g/personal/tongzhou_qiuyunus_onmicrosoft_com/EQQjgUcWmwlIqRZxezjG16ABQKTs17bazz5bLS9t0SSIhQ?e=wb8rUY

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[MFelix · Accepted solution]

Hi @Anonymous ,

There is one aspect of your setup that I don't understand but maybe is just a visual thing, you want a single table on the first page or two tables?

I have create the followin setup:

• Cost Center table  - to use as slicer
• Two measures:

Table 1 Filtering =
VAR selected_costcenter =
    ALLSELECTED ( CostCenter[Cost Center] )
RETURN
    IF ( NOT ( MAX ( Table1[Cost Center] ) IN selected_costcenter ); 1; 0 )

Table 2 Filtering =
VAR selected_costcenter =
    ALLSELECTED ( CostCenter[Cost Center] )
RETURN
    IF ( MAX ( Table1[Cost Center] ) IN selected_costcenter; 1; 0 )

Now if you use this two measure and filtered them on 1 you should get expected result.

See attach PBIX.

[Anonymous]

@MFelix  @Anonymous  It works!! 🙂 🙂  You both are a genius. Both solutions are very similar and serve the purpose. Replying to the MFelix point, If i want to view the output in a single table? That's right. I would be using the additional steps that Rico has detailed out really well. Again, Thanks a lot folks. Really mean it. Cheers.

[MFelix]

Hi @Anonymous ,

No genius here just glad to help out.

Please mark the correct answer so that other can also get help.