RankX not ranking properly

[Roberto456]

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 Category])

RETURN

RANKX(FILTER(ALLSELECTED('Amazon Data'),'Amazon Data'[Amazon Category]=Category),[Metric],,DESC,Dense)

[edhans]

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?

[Roberto456]

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]

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.

[Roberto456]

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]

Hi @Roberto456 ,

How about the result after you follow the suggestions mentioned in my original post?Could you please provide more details about it If it doesn't meet your requirement?

Best regards,

[v-lid-msft]

Hi @Roberto456 ,

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,