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)
      )
       
       
      • v-lid-msft's avatar
        v-lid-msft
        Icon for Community Support rankCommunity Support
        6 years ago

        Hi Anonymous ,

         

        We test your measure with a sample but it seems work fine on single table, what is the relationship between your fact table and dim table? Does the two field used in matrix chart com from the dim table? Could you please share the formula of [master] measure if it does not contain any confidential information?

         

         

        You can also try to use the following formula:

         

        Product Rank (Product) =
IF (
    ISINSCOPE ( '(Dim) Product'[Productsize_ID] ),
    VAR CURRENTPRODUCT =
        DISTINCT ( '(Dim) Product'[Product_Name] )
    RETURN
        RANKX (
            CALCULATETABLE (
                DISTINCT ( '(Dim) Product'[Productsize_ID] ),
                ALLSELECTED (),
                '(Dim) Product'[Product_Name] IN CURRENTPRODUCT
            ),
            CALCULATE ( [Master] ),
            ,
            DESC,
            DENSE
        ),
    RANKX (
        CALCULATETABLE ( DISTINCT ( '(Dim) Product'[Product_Name] ), ALLSELECTED () ),
        CALCULATE ( [Master] ),
        ,
        DESC,
        DENSE
    )
)

         


        By the way, PBIX file as attached.


        Best regards,