Forum Discussion

RMDNA's avatar
RMDNA
Icon for Solution Sage rankSolution Sage
8 years ago

Dynamic rankx() with filters

Hi all,   I've looked into other rankx() posts, but they seem too simple for what I need. Providing a PBIX would be difficult - this has a lot of tables and data, and I can't possible sanitize ever...
Need Help
MFelix's avatar
MFelix
Icon for Super User rankSuper User
8 years ago

Hi RMDNA,

 

When you use the ALL formula you ignore the filter you have on your reports, may they be at the visual level, page level or report level. To make your measure work with the selected records you need to use the ALLSELECTED in this case the rank is applied over all records that are within the slicer so the ranking is recalculated. Replace your measure by:

 

Rank = rankx(allselected('Table 1'), 'Table 1'[% of Total])

This should give what you need.

 

Regards,

MFelix

  • RMDNA's avatar
    RMDNA
    Icon for Solution Sage rankSolution Sage
    8 years ago

    MFelix, Zubair_Muhammad,

     

    Thanks for helping me better understand the ALL() variants - they always were a bit confusing.

     

    Your measures mirror the current rank:

     

     

     Does allselected(Table1) take into account all potential slicers from all tables, or just from those specified in the formula? My slicers are coming from all sorts of different locations.

     

    From looking at it more, it looks like my % of Total is off, which is casuing the rank issue. I need to figure that out first.

    • Zubair_Muhammad's avatar
      Zubair_Muhammad
      Icon for Community Champion rankCommunity Champion
      8 years ago

      RMDNA

       

      May be

       

      Rank = rankx(allselected('Table 1'[ColumnName]), 'Table 1'[% of Total])
    • MFelix's avatar
      MFelix
      Icon for Super User rankSuper User
      8 years ago

      Hi RMDNA,

       

      This depends on the context of your measures, can you please provide some sample data and the filters you want to apply.

       

      Regards,

      MFelix

      • RMDNA's avatar
        RMDNA
        Icon for Solution Sage rankSolution Sage
        8 years ago

        MFelix, Zubair_Muhammad,

         

        PBIX download

         

        I've attached a sample PBIX. The table names and relationships are the same, the data is fabricated. Hopefully the numbers are valid - if not, they can be edited in Query Editor.

         

        I'm looking for a working rank and running total, both of which respond to the slicers at the top of the page.

         

        Thanks for the help, guys. I know this is a lot of work, and you answer half the questions on the board.