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.
Community ChampionHi,
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.
Community ChampionMisunderstood 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.