Allow a column as a filter inside CALCULATETABLE but do not allow the same column used in a slicer

[mtomova]

Hi all,

I have a calculated measure in which I have applied specific filters in a CALCULATETABLE function.

One of the columns I have used as a filter in the CALCULATETABLE, also exists as a slicer on the report page.

What I want is to allow all other slicers on the page to filter the calculated measure, but not allow only this specific slicer to interact with the calculated measure.

At the same time the column used for the slicer exists as a filter in CALCULATETABLE...

SUMX(
    CALCULATETABLE(

         SUMMARIZE('WHS2 Material Line Measures Filtered', 
         [Property Num],
         [Measure Propery Type], 
         "CostQty", [Cost * QTY], 
         "Product Budget", MAX('My Table'[Product Budget]) ),

    'My Table'[Budget Performance] = "Under Budget"),
  
([Product Budget] - [CostQty]) 
)

So, [Budget Performance] also exists as a slicer in the report page.

What I want is for this slicer to no affect the calculated measure, but allow all other slicers on the page to work.

I hope this makes sense...

Thanks,

Maria

[johnt75 · Accepted solution]

What you're describing is the default behaviour. If you explicitly specify a filter on a column inside CALCULATE or CALCULATETABLE that will override any external filters on that column, so it will be as though the slicer doesn't exist.

If you need to change that behaviour you can use KEEPFILTERS to add your filters but keep the existing filters from slicers etc.

Reading your code there are some best practices you may not be aware. Firstly, you should never use SUMMARIZE to create calculated columns, always use ADDCOLUMNS instead.

When referring to columns it is best practice to use the table name as well, so that it is clear what is a column and what is a measure, as measures are always referred to without table names.

Finally, when creating new calculated columns within variables it is a good idea to prefix the name with @, so that you can easily see that it is a column name rather than a measure. It also prevents accidental clashes with the names of actual measures.

Below is your code rewritten using these best practices.

Sum of under budget =
SUMX (
    CALCULATETABLE (
        ADDCOLUMNS (
            SUMMARIZE (
                'WHS2 Material Line Measures Filtered',
                'WHS2 Material Line Measures Filtered'[Property Num],
                'WHS2 Material Line Measures Filtered'[Measure Propery Type]
            ),
            "@CostQty", [Cost * QTY],
            "@Product Budget", CALCULATE ( MAX ( 'My Table'[Product Budget] ) )
        ),
        'My Table'[Budget Performance] = "Under Budget"
    ),
    ( [@Product Budget] - [@CostQty] )
)

For more on best practices, and excellent content on DAX in general, read the articles at https://www.sqlbi.com/articles/ 

[DataNinja777 · Accepted solution]

Hi @mtomova ,

To make the Budget Performance slicer not affect your calculated measure while allowing all other slicers on the page to influence it, you can use the REMOVEFILTERS function in your DAX formula. This function clears the filter context for the specific column or table you specify.

Here’s how you can modify your formula:

SUMX(
    CALCULATETABLE(
        SUMMARIZE(
            'WHS2 Material Line Measures Filtered',
            [Property Num],
            [Measure Propery Type], 
            "CostQty", [Cost * QTY], 
            "Product Budget", MAX('My Table'[Product Budget])
        ),
        REMOVEFILTERS('My Table'[Budget Performance]),
        'My Table'[Budget Performance] = "Under Budget"
    ),
    ([Product Budget] - [CostQty]) 
)

The REMOVEFILTERS('My Table'[Budget Performance]) function ensures that the Budget Performance slicer does not affect the calculated measure. It removes any filter context applied by the slicer on this specific column.

The condition 'My Table'[Budget Performance] = "Under Budget" is explicitly applied in the CALCULATETABLE function, ensuring that the filter on Budget Performance is always set to "Under Budget" regardless of the slicer selection. Since the REMOVEFILTERS function is applied only to Budget Performance, all other slicers on the page will still interact with the measure and influence its calculation.

By incorporating REMOVEFILTERS, the measure is made independent of the Budget Performance slicer while remaining responsive to other slicers.

Best regards,

[johnt75 · Accepted solution]

What you're describing is the default behaviour. If you explicitly specify a filter on a column inside CALCULATE or CALCULATETABLE that will override any external filters on that column, so it will be as though the slicer doesn't exist.

If you need to change that behaviour you can use KEEPFILTERS to add your filters but keep the existing filters from slicers etc.

Reading your code there are some best practices you may not be aware. Firstly, you should never use SUMMARIZE to create calculated columns, always use ADDCOLUMNS instead.

When referring to columns it is best practice to use the table name as well, so that it is clear what is a column and what is a measure, as measures are always referred to without table names.

Finally, when creating new calculated columns within variables it is a good idea to prefix the name with @, so that you can easily see that it is a column name rather than a measure. It also prevents accidental clashes with the names of actual measures.

Below is your code rewritten using these best practices.

Sum of under budget =
SUMX (
    CALCULATETABLE (
        ADDCOLUMNS (
            SUMMARIZE (
                'WHS2 Material Line Measures Filtered',
                'WHS2 Material Line Measures Filtered'[Property Num],
                'WHS2 Material Line Measures Filtered'[Measure Propery Type]
            ),
            "@CostQty", [Cost * QTY],
            "@Product Budget", CALCULATE ( MAX ( 'My Table'[Product Budget] ) )
        ),
        'My Table'[Budget Performance] = "Under Budget"
    ),
    ( [@Product Budget] - [@CostQty] )
)

For more on best practices, and excellent content on DAX in general, read the articles at https://www.sqlbi.com/articles/