Forum Discussion

RENJITH_R_S's avatar
RENJITH_R_S
Resolver II
1 year ago
Solved

DAX 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...
  • kushanNa's avatar
    kushanNa
    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
    )