Forum Discussion
Helper IIIPage filter that affects two columns
Helper IIIHi 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.
Super UserHi 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.
- Saarek
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.
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:
**** 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.- MFelix
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,