Writing a calculation group designed to make a slicer inactive

[jonnybolton]

I have a slicer on `PeriodSlicer[Period]` for `All Time`, `Full Year`, `Year to Date` and `Selected Dates`.

`Selected Dates` should apply whatever dates are selected in the `Calendar` slicer, while the others should ignore the `Calendar` slicer and re-filter the dates accordingly. I have achieved this with a calculation group, and it works well for cards which aggregate all data in the relevant date period.

The problem I am having is making this work with a visual where `Calendar[Month Year]` is on the x-axis; I'm struggling with how to remove the `Calendar` slicer filter without also removing the row context of the visual.

My calculation group is as follows:

Apply Period Slicer = 
VAR SelectedPeriod = SELECTEDVALUE( PeriodSlicer[Period] )
VAR TodayDate = TODAY()
VAR CurrentYear = YEAR( TodayDate )
VAR MonthEnd = EOMONTH ( TodayDate, 0 )
RETURN
SWITCH(
    SelectedPeriod,
    "All Time",
        CALCULATE(
            SELECTEDMEASURE(),
            REMOVEFILTERS( 'Calendar' )
        ),
    "Full Year",
        CALCULATE(
            SELECTEDMEASURE(),
            FILTER(
                ALL( 'Calendar' ),
                YEAR( 'Calendar'[Date] ) = CurrentYear
            )
        ),
    "Year to Date",
        CALCULATE(
            SELECTEDMEASURE(),
            FILTER(
                ALL( 'Calendar' ),
                'Calendar'[Date] >= DATE ( CurrentYear, 1, 1 ) && 'Calendar'[Date] <= MonthEnd
            )
        ),
    "Selected Dates",
        SELECTEDMEASURE()
)

The visual's current behaviour is:
- Only months within the `Calendar` slicer range are showing
- Each month shows the same result; the value of the measure applied to the entire range defined by the `Period` slicer

What I want is:
- Any month defined by the `Period` slicer should show
- Each month's result should be the value for just that month

Is it possible to re-write the calculation group to achieve this?

[v-karpurapud]

Hi @jonnybolton 

We would like to confirm whether the issue has been resolved. If it is still outstanding, please share any additional information so we can assist you further.

Thank you.

[jonnybolton · Accepted solution]

Hi,

Thanks for following up. Unfortunately the provided solutions didn't work - I managed to solve this one myself by creating two copies of the Calendar table - one for use by the calculation group & calendar slicer, and a separate one for use in the graph's axis.

[v-karpurapud]

Hi @jonnybolton 

Thanks for reaching out to the Microsoft fabric community forum. 

I would like to express my appreciation @Poojara_D12 , @Kedar_Pande  and @danextian for your active participation in the community forum and for the valuable solutions you have shared. Your contributions have a significant impact.

I hope the information provided will assist you in resolving the issue. If you have further questions or require additional assistance, please do not hesitate to reach out. We are here to support you.

Best Regards, 
Community Support Team.

[danextian]

Hi @jonnybolton 

REMOVEFILTERS, ALL and such affect only the result of a measure but it doesn't modify the visibility of a row so if January is selected in the slicer only January will be visible in a related visual. Use a disconnected table instead.

Apply Period Slicer = 
VAR SelectedPeriod =
    SELECTEDVALUE ( PeriodSlicer[Period] )
VAR TodayDate =
    TODAY ()
VAR CurrentYear =
    YEAR ( TodayDate )
VAR _StartOfYear =
    DATE ( CurrentYear, 1, 1 )
VAR MonthEnd =
    EOMONTH ( TodayDate, 0 )
VAR _CYDates =
    CALCULATETABLE (
        VALUES ( DisconnectedDate[Date] ),
        DisconnectedDate[Year] = CurrentYear
    )
VAR _AllTime=
    CALCULATE (
        SELECTEDMEASURE(),
        TREATAS ( VALUES ( DisconnectedDate[Date] ), Dates[Date] ) 
    )
VAR _selectedDates =
    CALCULATE (
        SELECTEDMEASURE(),
        KEEPFILTERS ( TREATAS ( VALUES ( DisconnectedDate[Date] ), Dates[Date] ) )
    )
VAR _fullyear =
    CALCULATE ( SELECTEDMEASURE(),  TREATAS ( _CYDates, Dates[Date] ) ) 
VAR _YTD =
    CALCULATE (
        CALCULATE ( SELECTEDMEASURE(), DATESYTD ( Dates[Date] ) ),
        TREATAS ( _CYDates, Dates[Date] ) 
    )
RETURN
    SWITCH (
        SelectedPeriod,
        "All Time", _AllTime,
        "Full Year", _fullyear,
        "Year to Date", _YTD,
        "Selected Dates", _selectedDates
    )

Please see the attached pbix.

[Kedar_Pande]

@jonnybolton ,

Modify your calculation items to use KEEPFILTERS:

"All Time", CALCULATE(
SELECTEDMEASURE(),
KEEPFILTERS('Calendar'[Month Year]),
REMOVEFILTERS('Calendar'[Date])
),
"Full Year", CALCULATE(
SELECTEDMEASURE(),
KEEPFILTERS('Calendar'[Month Year]),
FILTER(
ALL('Calendar'[Date]),
YEAR('Calendar'[Date]) = CurrentYear
)
),
"Year to Date", CALCULATE(
SELECTEDMEASURE(),
KEEPFILTERS('Calendar'[Month Year]),
FILTER(
ALL('Calendar'[Date]),
'Calendar'[Date] >= DATE(CurrentYear, 1, 1) &&
'Calendar'[Date] <= MonthEnd
)
)

If this answer helped, please click Kudos or mark as Solution.
-Kedar
LinkedIn: https://www.linkedin.com/in/kedar-pande

[jonnybolton]

Thanks, this breaks completely because it's expecting 

'Calendar'[Month Year] to be boolean

[Poojara_D12]

Hi @jonnybolton 

I think you're very close, the behaviour you're seeing is what happens when the calculation group fully overrides the date context: by using REMOVEFILTERS('Calendar') or ALL('Calendar'), you wipe out both the slicer filter and the visual row context, so the engine cannot evaluate Month-by-Month anymore.

To fix this, your calculation group must remove slicer filters but preserve the row context from the visual axis. The right pattern is to use ALLSELECTED for expansion + KEEPFILTERS to let the Month context survive.

Apply Period Slicer =
VAR SelectedPeriod = SELECTEDVALUE( PeriodSlicer[Period] )
VAR TodayDate = TODAY()
VAR CurrentYear = YEAR( TodayDate )
VAR MonthEnd = EOMONTH ( TodayDate, 0 )
RETURN
SWITCH(
    SelectedPeriod,
    "All Time",
        CALCULATE(
            SELECTEDMEASURE(),
            ALLSELECTED('Calendar'),     -- remove slicer but keep axis
            KEEPFILTERS('Calendar')      -- keep each month's row context
        ),

    "Full Year",
        CALCULATE(
            SELECTEDMEASURE(),
            ALLSELECTED('Calendar'),
            KEEPFILTERS(
                FILTER(
                    ALL('Calendar'),
                    YEAR('Calendar'[Date]) = CurrentYear
                )
            )
        ),

    "Year to Date",
        CALCULATE(
            SELECTEDMEASURE(),
            ALLSELECTED('Calendar'),
            KEEPFILTERS(
                FILTER(
                    ALL('Calendar'),
                    'Calendar'[Date] >= DATE(CurrentYear, 1, 1) &&
                    'Calendar'[Date] <= MonthEnd
                )
            )
        ),

    "Selected Dates",
        SELECTEDMEASURE()
)

this is achievable.
The trick in calculation groups affecting date slicers + time intelligence is never fully clear the Calendar table; instead, clear slicer impact while preserving row context.

I hope it helps and you achieve it.

[jonnybolton]

Hi, thankyou for taking the time to suggest a solution. Unfortunately this has not worked. It didn't fix the monthly visual, and it also cause the cards to stop working properly - selections from the Calrnder slicer are impacting the cards

[Poojara_D12]

Hi @jonnybolton 

You can put this into your calculation group, change the 'Calendar'[Month Year] column name to match your model, use the date-key if you use numeric keys for sorting.

Apply Period Slicer =
VAR SelectedPeriod = SELECTEDVALUE( PeriodSlicer[Period] )
VAR TodayDate = TODAY()
VAR CurrentYear = YEAR( TodayDate )
VAR MonthEnd = EOMONTH( TodayDate, 0 )

-- Capture the axis context BEFORE we remove any filters.
-- Use the column that is actually on the visual axis (text or key).
VAR AxisMonths =
    IF(
        ISINSCOPE( 'Calendar'[Month Year] ),
        VALUES( 'Calendar'[Month Year] ),
        BLANK()
    )

-- Helper: reapply axis months to Calendar after we clear slicer filters.
VAR ReapplyAxis =
    IF(
        NOT( ISBLANK( AxisMonths ) ),
        TREATAS( AxisMonths, 'Calendar'[Month Year] ),
        BLANK()
    )

RETURN
SWITCH(
    SelectedPeriod,

    "All Time",
        IF(
            NOT( ISBLANK( AxisMonths ) ),
            -- Axis present: clear slicer but reapply per-month axis so each month evaluates individually
            CALCULATE(
                SELECTEDMEASURE(),
                ALL( 'Calendar' ),        -- clear existing calendar filters (including slicer)
                ReapplyAxis               -- reapply only the axis months
            ),
            -- No axis (cards): show full-time total
            CALCULATE(
                SELECTEDMEASURE(),
                ALL( 'Calendar' )
            )
        ),

    "Full Year",
        IF(
            NOT( ISBLANK( AxisMonths ) ),
            CALCULATE(
                SELECTEDMEASURE(),
                ALL( 'Calendar' ), -- remove slicer
                ReapplyAxis,
                -- enforce current year
                FILTER(
                    ALL( 'Calendar' ),
                    YEAR( 'Calendar'[Date] ) = CurrentYear
                )
            ),
            -- Cards: just full-year total
            CALCULATE(
                SELECTEDMEASURE(),
                FILTER(
                    ALL( 'Calendar' ),
                    YEAR( 'Calendar'[Date] ) = CurrentYear
                )
            )
        ),

    "Year to Date",
        IF(
            NOT( ISBLANK( AxisMonths ) ),
            CALCULATE(
                SELECTEDMEASURE(),
                ALL( 'Calendar' ),
                ReapplyAxis,
                FILTER(
                    ALL( 'Calendar' ),
                    'Calendar'[Date] >= DATE( CurrentYear, 1, 1 ) &&
                    'Calendar'[Date] <= MonthEnd
                )
            ),
            CALCULATE(
                SELECTEDMEASURE(),
                FILTER(
                    ALL( 'Calendar' ),
                    'Calendar'[Date] >= DATE( CurrentYear, 1, 1 ) &&
                    'Calendar'[Date] <= MonthEnd
                )
            )
        ),

    -- Selected Dates: honor whatever the Calendar slicer / selection is (normal behaviour)
    SELECTEDMEASURE()
)

If your axis uses a numeric month key (e.g., MonthKey) use that column in ISINSCOPE, VALUES, and TREATAS for reliability and correct sorting.

If you use hierarchies (Year → Month) you may also need ISINSCOPE('Calendar'[Year]) checks to handle year-only or other granularities, or capture multiple fields (Year + MonthKey) and TREATAS them together.

If performance is a concern, consider capturing a month key instead of a text label and ensure Calendar is single column key-indexed.

[jonnybolton]

Thankyou. This gives the error 'The TREATAS function expects a table expression for argument '', but a string or numeric expression was used.'