Issues propagating the query context into a CALCULATE nested inside a RANKX function

[Maikeru]

Dear Team,

I am trying to create a Rank column (not measure) based on the total sales per customer in my dataset.

Rank column = 
RANKX(
    ALLSELECTED('Dataset'[Customer]), 
    CALCULATE(
        SUM('Dataset'[Sales]),
        FILTER(ALLSELECTED('Dataset'), 
        'Dataset'[Customer] = EARLIER('Dataset'[Customer])
        )
    ), ,
    DESC)

The formula worked fine until... I apply a query filter (say on the slicer on the material column).
I have tried to debug the formula quite a bit but I have the impression, that DAX cannot apply the ALLSELECTED query filter when the FILTER is nested inside the RANKX + CALCULATE functions.

What makes me think that is that the following formula works fine.

Sales by Customer = 
CALCULATE(
    SUM('Dataset'[Sales]),
    FILTER(ALLSELECTED('Dataset'),
    'Dataset'[Customer] = MAX('Dataset'[Customer])
    ) 
)

Thanks to all in advance!

[Maikeru]

@v-yuezhe-msft

Thank you very much for taking the time to look into my issue!

This is exactly the result I am expecting except the Rank needs to be column and not a measure.

(Because I want to apply a RunningSum based on the rank column).

Best regards

Michael

[v-yuezhe-msft · Accepted solution]

@Maikeru,

Calculated columns don't respond to slicer selections, please check this KB.

Regards,
Lydia

[Maikeru]

@v-yuezhe-msft

Oh thanks Lydia, I missed out this key concept.

I will then think to achieve what I am looking for.

Thanks for your help.

Best regards

Michael

[v-yuezhe-msft]

@Maikeru,

Please check the DAX in the following PBIX file.

https://1drv.ms/u/s!AhsotbnGu1Nok2nX8pAE9CsexCEn

Regards,
Lydia