Forum Discussion

seniorathlete's avatar
seniorathlete
Regular Visitor
1 year ago

Dynamic Top 10 - Using Parameter to Keep/Exclude Value

Hello All, I'm trying to create a visual that shows the top 10 conditions by counts per year that also has a parameter that allows me to include COVID-19 or not. Since COVID-19 case counts are so la...
Need Help
Tips and Tricks
Anonymous's avatar
Anonymous
Not applicable
1 year ago

Hi DataNinja777 ,thanks for the quick reply, I'll add more.

Hi seniorathlete ,

Parameter-like effects can be achieved using a slicer.

Please follow these steps:

1.Create 'Table 2' by manually entering the following data

2.Use the following DAX expression to create measures

 

TotalCases = 
VAR _includeCovid19 = SELECTEDVALUE('Table 2'[IncludeCovid19])
RETURN
IF(_includeCovid19 = "Yes",
    SUMX('Table',[cases]),IF(_includeCovid19 = "No",SUMX(FILTER('Table',[disease] <> "COVID-19"),[cases]))
)
Top10 = 
IF(ISBLANK([TotalCases]),BLANK(),RANKX(ALLEXCEPT('Table','Table'[year]),[TotalCases],,DESC,Dense))

 

3.Make the measure “Top10” available in the filter pane and set it to less than or equal to 10.

4.Use the following DAX expression to create a measure

 

Measure = (SELECTEDVALUE('Table'[year]) * 100 + [Top10]) * -1

 

Turn off the following options and add the measure to the visual 

Place the mouse over the marker position, the “<-||->” symbol will appear and drag it to the left

Final output