Forum Discussion
Top 5 diagnosis
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.
5 Replies
- bcdobbs
Community Champion
Can you supply a demo pbix file?
- 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
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.
sorry can you try the code
i did not work with me
thank you so much
but if i publish this dashboard in the command center it will not show the top 5 diagnosis
any recommandations?
