Changing Active Filters in Calculate

[dhestarwirawan]

Hi,

I have the "Score Rank" measure which calculate the Rank of Aggregate Score measure on all providers while trying to incorporate all the filters. This is the formula for "Score Rank" measure:

Rank Score = RANKX(ALLEXCEPT(Claim_Data_Cleaned, Claim_Data_Cleaned[Provider Rating], Claim_Data_Cleaned[Region], Claim_Data_Cleaned[Period]), CALCULATE([Aggregate Score],ALLEXCEPT(Claim_Data_Cleaned,Claim_Data_Cleaned[Provider],Claim_Data_Cleaned[Period],Claim_Data_Cleaned[Provider Rating],Claim_Data_Cleaned[Region])),,DESC,Dense)

Now, I want to compare current period rank (2024H1) to previous period rank (2023H2). So I try to changing the active filter for Period to "2023H2". I write the formula as below:

prev_rank = CALCULATE([Rank Score], KEEPFILTERS(Claim_Data_Cleaned[Period] = "2023H2"))

But, I got rank 1 for all Provider using above formula.

What did I miss on my "prev_rank" formula?

Thank you.

[Anonymous]

Hi @dhestarwirawan 

It seems like the issue with your "prev_rank" formula is that it is not correctly preserving the context of each provider when you attempt to calculate the rank for the previous period.

Here's a revised version of the "prev_rank" measure that should fix the issue:

prev_rank = 
CALCULATE(
    [Rank Score],
    KEEPFILTERS(Claim_Data_Cleaned[Period] = "2023H2"),
    ALLEXCEPT(Claim_Data_Cleaned, Claim_Data_Cleaned[Provider])
)

Best Regards,

Jayleny

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...