The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
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.
Solved! Go to 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:
Did I answer your question? If yes, pls mark my post as a solution and appreciate your Kudos !
Thank you~
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:
Did I answer your question? If yes, pls mark my post as a solution and appreciate your Kudos !
Thank you~
User | Count |
---|---|
15 | |
8 | |
6 | |
6 | |
5 |
User | Count |
---|---|
23 | |
13 | |
13 | |
8 | |
8 |