Forum Discussion

RENJITH_R_S's avatar
RENJITH_R_S
Icon for Resolver II rankResolver 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...
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
)

     

     

Akash_Varuna's avatar
Akash_Varuna
Icon for Super User rankSuper 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
RENJITH_R_S's avatar
RENJITH_R_S
Icon for Resolver II rankResolver II
1 year ago

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

 

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