Dynamic Top 10 - Using Parameter to Keep/Exclude Value

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,