Forum Discussion
RENJITH_R_S
Resolver II
1 year agoDAX for Slicer Selection Values
Hello Please help me on this issue. I m using a slicer with 2 columns Fiscal Year and Fiscal Quarter Year. Consider Fiscal year column contains values 2024, 2025, 2026. and Fiscal Quarter Year c...
- 1 year ago
Hi RENJITH_R_S
Try this measure
Selected Periods = VAR SelectedYears = VALUES('Date'[Fiscal Year]) VAR SelectedQuarters = VALUES('Date'[Fiscal Quarter Year]) -- Table: Year and number of selected quarters in that year VAR QuarterCountsPerYear = SUMMARIZE( 'Date', 'Date'[Fiscal Year], "SelectedQuarterCount", CALCULATE( DISTINCTCOUNT('Date'[Fiscal Quarter Year]) ) ) -- Filter to get only years where 4 quarters are selected VAR FullYears = SELECTCOLUMNS( FILTER(QuarterCountsPerYear, [SelectedQuarterCount] = 4), "Fiscal Year", [Fiscal Year] ) -- Quarters NOT part of full selected years VAR QuartersToShow = EXCEPT( SelectedQuarters, SELECTCOLUMNS( FILTER('Date', 'Date'[Fiscal Year] IN FullYears), "Fiscal Quarter Year", 'Date'[Fiscal Quarter Year] ) ) -- Combine into final display strings VAR DisplayYears = CONCATENATEX(FullYears, [Fiscal Year], ", ") VAR DisplayQuarters = CONCATENATEX(QuartersToShow, [Fiscal Quarter Year], ", ") RETURN TRIM( DisplayYears & IF(DisplayYears <> "" && DisplayQuarters <> "", ", ", "") & DisplayQuarters )
kushanNa
Super User
1 year agoHi RENJITH_R_S
Try this measure
Selected Periods =
VAR SelectedYears = VALUES('Date'[Fiscal Year])
VAR SelectedQuarters = VALUES('Date'[Fiscal Quarter Year])
-- Table: Year and number of selected quarters in that year
VAR QuarterCountsPerYear =
SUMMARIZE(
'Date',
'Date'[Fiscal Year],
"SelectedQuarterCount",
CALCULATE(
DISTINCTCOUNT('Date'[Fiscal Quarter Year])
)
)
-- Filter to get only years where 4 quarters are selected
VAR FullYears =
SELECTCOLUMNS(
FILTER(QuarterCountsPerYear, [SelectedQuarterCount] = 4),
"Fiscal Year", [Fiscal Year]
)
-- Quarters NOT part of full selected years
VAR QuartersToShow =
EXCEPT(
SelectedQuarters,
SELECTCOLUMNS(
FILTER('Date', 'Date'[Fiscal Year] IN FullYears),
"Fiscal Quarter Year", 'Date'[Fiscal Quarter Year]
)
)
-- Combine into final display strings
VAR DisplayYears =
CONCATENATEX(FullYears, [Fiscal Year], ", ")
VAR DisplayQuarters =
CONCATENATEX(QuartersToShow, [Fiscal Quarter Year], ", ")
RETURN
TRIM(
DisplayYears &
IF(DisplayYears <> "" && DisplayQuarters <> "", ", ", "") &
DisplayQuarters
)
RENJITH_R_S
Resolver II
1 year agokushanNa -Excellent, It works. Thanks