Forum Discussion

Saarek's avatar
Saarek
Icon for Helper III rankHelper III
5 years ago

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.

Tutorial Requests

6 Replies

  • MFelix's avatar
    MFelix
    Icon for Super User rankSuper User
    5 years ago

    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.

    • Saarek's avatar
      Saarek
      Icon for Helper III rankHelper III
      5 years ago

      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.

      • MFelix's avatar
        MFelix
        Icon for Super User rankSuper User
        5 years ago

        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.