Dynamic Top 10 - Using Parameter to Keep/Exclude Value

[seniorathlete]

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 large, it dwarfs/masks the trends in the other condition. Basically I want to have a parameter that is yes/no - yes includes COVID-19 in the top 10 rankings, or no exclude COVID-19 from the top 10 rankings. And In my rankings I always want to have 10 conditions.

Fake data:

year	disease	cases
2020	COVID-19	1
2020	influenza	5
2020	measles	1
2021	COVID-19	5
2021	influenza	2
2021	measles	1
2022	COVID-19	10
2022	influenza	5
2022	measles	3
2023	COVID-19	4
2023	influenza	10
2023	measles	5

[Anonymous]

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

[DataNinja777]

Hi @seniorathlete ,

To create a visual that dynamically displays the top 10 conditions by case counts per year while allowing the inclusion or exclusion of COVID-19, you can use a combination of a parameter and DAX measures in Power BI.

First, create a parameter named Include COVID-19 with two options: "Yes" and "No." This parameter will allow users to toggle whether COVID-19 is included in the rankings. Once the parameter is created, it can be added as a slicer in the report to provide interactivity.

Next, define a DAX measure to calculate the top 10 conditions dynamically based on the parameter value. The measure should filter the dataset to exclude COVID-19 when the parameter is set to "No" while still ensuring that the top 10 conditions are displayed for each year.

Top 10 Conditions = 
VAR IncludeCOVID = SELECTEDVALUE('Include COVID-19'[Include COVID-19])
VAR FilteredTable =
    IF(
        IncludeCOVID = "Yes",
        'Disease Data',
        FILTER('Disease Data', 'Disease Data'[disease] <> "COVID-19")
    )
VAR RankedConditions =
    ADDCOLUMNS(
        SUMMARIZE(
            FilteredTable,
            'Disease Data'[year],
            'Disease Data'[disease]
        ),
        "@TotalCases", SUM('Disease Data'[cases])
    )
VAR Top10 =
    TOPN(10, RankedConditions, [@TotalCases], DESC)
RETURN
    Top10

The measure begins by checking the selected value of the parameter. If the parameter is set to "Yes," the entire dataset is used. If the parameter is set to "No," the dataset is filtered to exclude rows where the disease is "COVID-19." The SUMMARIZE function is then used to group the data by year and disease, calculating the total cases for each combination. The TOPN function is applied to extract the top 10 conditions based on the total cases in descending order.

To visualize the data, use a table or matrix visual in Power BI. Add the year and disease columns as rows and the cases column as values. Apply the Top 10 Conditions measure as a filter on the visual, ensuring only the top 10 conditions are displayed for each year. Place the parameter slicer on the report to allow users to toggle the inclusion of COVID-19 dynamically.

When the parameter is set to "Yes," COVID-19 will appear in the top 10 conditions if its case count qualifies. When the parameter is set to "No," COVID-19 will be excluded, and the next top-ranking condition will replace it, ensuring that the visual always displays 10 conditions per year. This approach provides flexibility in analyzing the trends of other conditions without the overwhelming dominance of COVID-19 case counts.

Best regards,

[seniorathlete]

Thanks for the response. Using this solution I get the "expression refers to multiple columns. multiple columns cannot converted to a scalar value" error message.