Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

RankX not ranking properly

Hello All.   Im getting werid ranking Values on my report.  notice how it jsut skips ranks for no reason.   Below is my Dax:   Rank (Category) = VAR Category = MAX('Amazon Data'[Amazon Categ...
Need Help
edhans's avatar
edhans
Icon for Community Champion rankCommunity Champion
6 years ago

It is something I think in your [Metric] measure. When you use a measure inside of a measure Power BI wraps CALCULATE() round it and does a context transition, basically redoing the filters. Can you share that measure with us as well?

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Sure:

     

     

    Metric = SWITCH(MAX('Switch Metric'[ID]),1,[Est (Monthly Profit)],2,[Est (Monthly Qty Sold)],3,[Match Quality],4,[Price (30 Days)],5,[Price (90 Days)],6,[Price (Amazon W/ Ship)],7,[Price (Profit)],8,[Sales Rank],9,[Sales Rank (30 Days)],10,[Sales Rank (90 Days)],11,[Rating],12,[Reviews],13,[ROI],14,[Price (Net)],15,[Price (Ours)])
     
     
    Est (Monthly Qty Sold) = CALCULATE(SUM('Amazon Data'[Est (Monthly Qty Sold)]))
    • edhans's avatar
      edhans
      Icon for Community Champion rankCommunity Champion
      6 years ago

      Ok. We are going down a rabbit hole here. This is your measure:

       

      Metric =
SWITCH(
    MAX( 'Switch Metric'[ID] ),
    1, [Est (Monthly Profit)],
    2, [Est (Monthly Qty Sold)],
    3, [Match Quality],
    4, [Price (30 Days)],
    5, [Price (90 Days)],
    6, [Price (Amazon W/ Ship)],
    7, [Price (Profit)],
    8, [Sales Rank],
    9, [Sales Rank (30 Days)],
    10, [Sales Rank (90 Days)],
    11, [Rating],
    12, [Reviews],
    13, [ROI],
    14, [Price (Net)],
    15, [Price (Ours)]
)

       

       

      Which, in turn, references 15 other measures, and everyone of those gets a context transition when it is done, and may or may not have their own filters being set internally.

      Kinda at a loss as to where to go from here. I'd need the PBIX file to go further, but this seems it will require some time to dig into this unless someone smarter than me (low hurdle) sees something more obvious in your explanation so far.

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        None of the other measures mentioned have filter applied to them in their DAX.

         

        I tried doing the same RankX again on a different document and referenced a Dimension table rather than the Fact table and it seems to work that way.

         

        Although I cant get the Rank to work on the product category Line. The 2nd product should Say "2"

         

        Dax Below:

         

         
        Product Rank (Product) =

        IF(HASONEVALUE('(Dim) Product'[Productsize_ID]),

        VAR CURRENTPRODUCT = MAX('(Dim) Product'[Product_Name])
        RETURN
        RANKX(
        FILTER(ALLSELECTED('(Dim) Product'),'(Dim) Product'[Product_Name] = CURRENTPRODUCT),[Master],,DESC,Dense)

        ,RANKX(ALLSELECTED('(Dim) Product'[Product_Name]),[Master],,DESC,Dense)
        )