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 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

Need Help
  • 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
)

     

     

6 Replies

  • Akash_Varuna's avatar
    Akash_Varuna
    Super User
    1 year ago

    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
    )
)

  • Akash_Varuna's avatar
    Akash_Varuna
    Super User
    1 year ago

    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
      • kushanNa's avatar
        kushanNa
        Super User
        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
)