Top 5 diagnosis

[razan_5r5]

[razan_5r5]

thank you so much 

but if i publish this dashboard in the command center it will not show the top 5 diagnosis 

any recommandations?

[razan_5r5]

sorry can you try the code 

i did not work with me

[bcdobbs]

Hi,
I would do the following:

1) Split geography columns off into a separate dimension table:

Geography = 
SUMMARIZE(
    SheetData,
    SheetData[District],
    SheetData[City],
    SheetData[Region],
    SheetData[latitude ],
    SheetData[Longitude]
)

Relate this back to your main table on the District column.

(Ideally split the whole table off into a star schema as it keeps the DAX much simpler: Importance of Star Schemas ) in Power Bi  

2) Swap all visuals to use columsn from this table.

3) Create a ranking measure in your main (fact) table:

District Rank = 
    RANKX ( 
        ALLSELECTED( Geography ),
        [Diagnosis Count]
    )

4) In the visual filter of for your map put this measure and set it so it is less than or equal to 5.

[bcdobbs · Accepted solution]

Misunderstood requirement. 

To see top 5 diagnosis list as a tooltip on map you need a measure like:

Top5 = 
VAR RankingContext = VALUES ( 'Sheet_4_data (6)'[Diagnosis Code] )
VAR Top5Diagnosis = 
CALCULATETABLE (
    VALUES( 'Sheet_4_data (6)'[Diagnosis Code] ),
    TOPN ( 5, ALL ( 'Sheet_4_data (6)'[Diagnosis Code] ), 'Sheet_4_data (6)'[diagnosis_count] ),
    RankingContext
)

RETURN CONCATENATEX(Top5Diagnosis, 'Sheet_4_data (6)'[Diagnosis Code], ", ")

Which can be dropped into the tooltip section.

[bcdobbs]

Can you supply a demo pbix file?