Display Rank Value In A Card While Including and Excluding Slicers

[bprokop]

In the attached PBIX, I am trying to create a measure for the Dynamic Rank All Card that will display the Rank for the Hospital ID but all it to change based on any other Slicer Selected.  Currently, I am using the following formula for RANK in the Card.

Dynamic Rank All =

RANKX(

    ALL(Data),

    CALCULATE(PRODUCT(Data[National Rank])),

    ,ASC

)

This will only display the Overall (National) Rank when I select a Hospital ID.  It will not change when I make a selection in Slicers (Geo Class, Hospital Type, City, State, etc.).  I want the Dynamic Rank Overall to change when I make a selection in this Slicers.  How can I adjust the formula in the measure to make this work.

Example: Hospital ID = 100001 | UF Health Jacksonville.  This results in the Dynamic Rank All to equal 3636 but if I filter Hospital Type by Short Term Acute Care it does not change.  With this filter on I want the value to change to 2317 to reflect the rank against all Short Term Acute Care Hospital.

Thanks in advance for your help.

Link Updated:

https://drive.google.com/file/d/1I8KYamW8BQe89fNQLKBzSoQl7I667aLt/view?usp=sharing

[Ashish_Mathur]

There is absolutely no relation between the text of your message and data in the file.  When i filter on UF Health Jacksonville, i do not see either 3636 or 2317 anwhere in the Table visual.  All i see is 49.

[bprokop]

Hi Ashish,

That appears to be one of the older files I had posted with a different question.  The file should be the one labeled Sample Data - Dynamic Rank.  I checked the google drive link and it brought me to the file.  Below is what you should see when opened.

Thanks

Brian

[Ashish_Mathur]

Hi,

I still see the same old file there.

[bprokop]

Hi Ashish,

Try the link below.  I reposted it to Google Drive.

https://drive.google.com/file/d/1I8KYamW8BQe89fNQLKBzSoQl7I667aLt/view?usp=sharing

[Ashish_Mathur]

Am unable to solve it.

[littlemojopuppy]

😅

[littlemojopuppy]

@Ashish_Mathur that is so unlike you!  You usually clear up the last few details after someone else does the heavy work!  Take your moment, dig in and fix this!!!  Shine, brother, shine!  😄

@bprokop do me a favor.  PM me with the details and the ability to download some data to play with this.  I'll solve this.

[littlemojopuppy]

Hi @bprokop 

There are some "new" (within the last year) window functions that are excellent at determining RANK based on selections.  Take a look at those.

[littlemojopuppy · Accepted solution]

Morning @bprokop 

Try this measure...

VAR SelectedHospitals =
    CALCULATETABLE (
        ALLSELECTED ( Data ),
        REMOVEFILTERS ( Data[Hospital ID] )
    )
RETURN
    RANK (
        SKIP,
        SelectedHospitals,
        ORDERBY ( Data[National Rank], ASC )
    )

I think this is the desired result?