Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Saarek
Helper III
Helper III

Page filter that affects two columns

I have two matrix pivot tables, these pivot tables are identical apart from a single column.

 

The values within the columns that differ have the same values, but in a different order.

 

So, each contains the following four values: Tier 1, Tier 2, Tier 3, Tier 4.

 

Column 1 is called LTA_Actual

Column 2 is called LTA_Plan

 

As the data matures the tier that the data has updates, which is why we have the two columns and two tables.

 

Ideally I'd like to have a page filter that automatically allows the user to filter to the tiers in both tables at once without having to manually set one or the other. From the user perspective they are aware we have plan and actual, but they just want to perform a single click.

 

I did try making a lookup table, but this did not seem to work.

6 REPLIES 6
Saarek
Helper III
Helper III

Saarek_0-1608036761977.png

 

MFelix
Super User
Super User

Hi @Saarek ,

 

Without knowing the full details of your model is difficult to give the best answer.

 

Are the tiers based on the same data tables or different datasoureces?

Is the calculation made based on measures or a column value?

 

Can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.

If the information is sensitive please share it trough private message.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Hi There @MFelix , sorry, I should have been more specific. There is a single table which contains both of the columns and the Matrix is all based on the same single data table.

 

Best way to describe it is that we have a single financial column and then two of these "Tier" columns. In effect if I apply a filter for Tier 1 I want both Pivot Matrix tables to filter themselves to their own Tier 1.

 

The tiers are within the rows section and the other items within the Values area are identical.

 

Currently I have a filter for LTA_Actual and LTA_Plan, but as the same dataset if I filter on either of those the other pivot table is wrong.

 

What I want is a single filter that will set Pivot Table A to LTA_Actual and Pivot Table B to LTA_Plan. This works if I manually filter the pivot tables themselves, but I ideally want a page filter.

Hi @Saarek ,

 

If I understand corretly you have a similar setup to this one:

 

Cat Value Tier A Tier B
A 100 Tier 1 Tier 3
B 200 Tier 2 Tier 3
C 150 Tier 1 Tier 4
D 300 Tier 3 Tier 1
E 100 Tier 2 Tier2

 

Now you want to select 1 tier on a slicer or similar and both visualizations show that Tier.

 

I would do the following:

  • Create a new table with the TIERS:

Tier

Tier 1
Tier 2
Tier 3
Tier 4

 

  • Add the following measures:

 

Tier A =
CALCULATE (
    SUM ( Data[Value] );
    FILTER ( ALLSELECTED ( Data[Tier A] ); Data[Tier A] IN VALUES ( Tiers[Tier] ) )
)

Tier B =
CALCULATE (
    SUM ( Data[Value] );
    FILTER ( ALLSELECTED ( Data[Tier B] ); Data[Tier B] IN VALUES ( Tiers[Tier] ) )
)

 

 

  • Now setup your matrix using the Tiers from the Tier table and the two measures result in attach PBIX.
  • You can also add a slicer fo facilitate the filtering.
  • If you have more than one measure then you can use the calculation groups to make only two additonal measures instead of  2 for each of your measures.

Tiers_Filter.gif

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Hi @MFelix , thank you for your help so far! I've tried opening your .pbix file, unfortunately my desktop version is older than yours and as I'm on a corporate computer I cannot update.

 

Below is a screen shot with dummy data of what the view currently looks like.

 

Saarek_0-1608204373633.png

Your code looks like it would allow me to work around and make this work with a single filter/slicer, but I am getting the following error:

 

Saarek_1-1608204486222.png

**** EDIT****

Code now works when I replace the semi colon with commas.

So, your solution works, but seemingly only if I can move all my sums to the new table. But there are lots of columns people want to filter by and I can't move them all to the new table.

Hi @Saarek ,

 

The error you are getting is related with regional settings you need to change the dot comma ";" by "," and it will work fine has you refer.

 

Not understanding the part below:


So, your solution works, but seemingly only if I can move all my sums to the new table. But there are lots of columns people want to filter by and I can't move them all to the new table.

 

The new table does not need to have any other data than the TIERS using the measures you are maknig the relation between the tiers and the calculations, what you can do in order not to have several measures that need to be redone based on the tiers you can use calculated groups.

 

But can you show a mockup of your data tables? That way I can adjust the process if needed,


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.