DAX for Slicer Selection Values

[RENJITH_R_S]

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 contains values q1 2024,q2 2024,q3 2024,q4 2024,q1 2025, q2 2025,q3 2025,q4 2025,q1 2026,q2 2026,q3 2026,q4 2026.

I need a dax formula for showing the selected values.

Case 1 - if user selects 2024 and 2025 on Fiscal Year column then need to show 2024, 2025
Case 2 - if user selects 2024 on Fiscal Year (automatically 4 quarters also selected) and q1 2025, q2 2025 is selected from Fiscal Quarter Year column then need to show 2024, q1 2025, q2 2025

[Akash_Varuna]

@RENJITH_R_S Could you try this then 

Selected Values =
VAR SelectedItems = 
    CONCATENATEX(
        DISTINCT('Table'[Fiscal Year]),
        'Table'[Fiscal Year] & 
            IF (
                COUNTROWS(
                    FILTER(
                        ALL('Table'),
                        'Table'[Fiscal Year] = EARLIER('Table'[Fiscal Year])
                    )
                ) > 1,
                ": " & CONCATENATEX(
                    FILTER(
                        DISTINCT('Table'[Fiscal Quarter Year]),
                        LEFT('Table'[Fiscal Quarter Year], 4) = 'Table'[Fiscal Year]
                    ),
                    'Table'[Fiscal Quarter Year],
                    ", "
                ),
                ""
            ),
        "; "
    )

RETURN
SelectedItems

[RENJITH_R_S]

@Akash_Varuna Not correct see the output. the output I want for this case is 2024, Q1 2025

[kushanNa · Accepted solution]

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
)

[RENJITH_R_S]

@kushanNa -Excellent, It works. Thanks

[RENJITH_R_S]

@Akash_Varuna Thanks for the input but I m using a single slicer not 2 slicers. In a single slicer, Hierarcy Mode method

[Akash_Varuna]

Hi @RENJITH_R_S 

Selected Values = 
VAR SelectedYears = CONCATENATEX(VALUES('Table'[Fiscal Year]), 'Table'[Fiscal Year], ", ")
VAR SelectedQuarters = CONCATENATEX(VALUES('Table'[Fiscal Quarter Year]), 'Table'[Fiscal Quarter Year], ", ")

RETURN
IF (
    ISFILTERED('Table'[Fiscal Year]) && ISFILTERED('Tbale'[Fiscal Quarter Year]),
    SelectedYears & ", " & SelectedQuarters,
    IF (
        ISFILTERED('Table'[Fiscal Year]),
        SelectedYears,
        SelectedQuarters
    )
)