DAX Modification for Precise Year Selection in Power BI Slicer

[0Experience]

Hello,

I have a straightforward chart with a slicer for years (2021-2025) and a card. When I select a year from the slicer, it displays that year on the card. If I select multiple years, it shows the range of those years on the card.

However, I've encountered an issue. When I select non-consecutive years, the card displays a range that includes years I didn't select. For instance, if I select 2021, 2022, and 2024, the card erroneously shows 2021-2024, including 2023 which I didn't select.

I know my code has been written the way it is showing, and need to modify the code.

DAX Code:

Selected Years = 
VAR SelectedYearCount = COUNTROWS(VALUES('Table'[Year]))
RETURN
IF(
    SelectedYearCount = 1,
    CONCATENATEX(VALUES('Table'[Year]), 'Table'[Year], ", "),
    VAR MinYear = MIN('Table'[Year])
    VAR MaxYear = MAX('Table'[Year])
    RETURN MinYear & "-" & MaxYear
)

My desired output would be more precise. For example,

Selecting 2021, 2022, and 2024 should display 2021-2022, 2024. 

Selecting 2021, 2023, and 2025 should show each individual year: 2021, 2023, 2025.

Selecting 2021, 2023, 2024, and 2025, the card should display 2021, 2023-2025, omitting the non-selected year, 2022.

I'm seeking guidance on how to modify my DAX to achieve this desired behavior. Please find the Power BI file attached for reference (Power BI File). Any assistance would be greatly appreciated.

[0Experience]

@xifeng_L thanks it works

[xifeng_L · Accepted solution]

Hi @0Experience ,

You can try below measure.

Selected Years = 
VAR SelectYearList = ALLSELECTED('Table'[Year])
RETURN
CONCATENATEX(
    SUMMARIZE(
        ADDCOLUMNS(
            SelectYearList,
            "Grp",
                'Table'[Year]-RANKX(SelectYearList,'Table'[Year],,ASC)
        ),
        [Grp],
        "Year Seq",
            VAR MinYear = MIN('Table'[Year])
            VAR MaxYear = MAX('Table'[Year])
            RETURN
            IF(MinYear=MaxYear,MinYear,MinYear&"-"&MaxYear)
    ),
    [Year Seq],
    ","
)

#Result:

Demo - Year_chart.pbix

Did I answer your question? If yes, pls mark my post as a solution and appreciate your Kudos !

Thank you~