Calculation group remove filter

DAX DAX Calculations
1

In tabular editor I created calculated groups: Time Intelligence with measures -7 Day, -1 Day.
E.g. -1 Day: CALCULATE(
SELECTEDMEASURE(),DATEADD(‘DWH DimDate’[DateName],-1,DAY))

In the report, I would like some measures not to work with calculated groups. Measure Removefilters and All not working.

2

Hello @Harris,

Thank You for posting your query onto the Forum.

In case, you don’t want a Calculation Group to be applied to certain measures then you need to edit your each calculation items by writing a measure like this inside your Tabular Editor -

IF(
    ISSELECTEDMEASURE( 
        [Total Sales By Order Date] , 
        [Total Costs] , 
        [Total Profits] ) , 
    CALCULATE( SELECTEDMEASURE() ,
        DATEADD( Dates[Date] , -7 , DAY ) ) )

This will consider only those measures on which you want to apply Calculation Groups effect. That is, in this case, “ISSELECTEDMEASURE()” measure will check the list of measures mentioned inside it on which you want the effect to be applied and if that measure is not found inside the list then that’ll not be evaluated and showcased as blanks.

Alternatively, you can write the same logic as -

IF(
    ISSELECTEDMEASURE( [Total Sales By Shipping Date] ) , 
    BLANK() ,
    CALCULATE( SELECTEDMEASURE() ,
        DATEADD( Dates[Date] , -7 , DAY ) ) )

In this case, I’m specifically mentioning only the measure that needs to be excluded from evaluation of Calcualtion Groups. So any measure not mentioned inside this list will be evaluated.

So it’s best to go with option no. 1 where we specifically mention only the list of those measures that needs to be considered for evaluation by Calculation Groups rather than option no. 2.

Or else if you don’t want to showcase those measures as blanks then write it like this -

IF(
    ISSELECTEDMEASURE( 
        [Total Sales By Order Date] , 
        [Total Costs] , 
        [Total Profits] ) , 
        
    CALCULATE( SELECTEDMEASURE() ,
        DATEADD( Dates[Date] , -7 , DAY ) ) , 
        
        [Total Sales By Shipping Date] )

In this case, only the measures mentioned inside the list of “ISSELECTEDMEASURE()” will change and the measures outside the list will showcase the original values and will not change at all.

I’m also attaching the working of the PBIX file for the reference purposes wherein I’ve created 4 calculation items inside a calculation group called “Time Intelligence”.

1). Calculation item related to “Current Day” will showcase all the measures as it is.

2). Calculation item related to “Last 7 Days” will evaluate all the measures except “[Sales by Shipping Date]”. This measure will showcase the original figures as it is.

3). Calculation item related to “Last 14 Days” and “Last 21 Days” will evaluate all the measures except “[Sales by Shipping Date]”. In this case, this measure will be evaluated as blanks.

Hoping you find this useful and meets your requirements that you’ve been looking for.

Thanks and Warm Regards,
Harsh

Excluding Measures From Calculation Groups - Harsh.pbix (3.5 MB)

1 Like
3

Thanks for the tips. Unfortunately, there are two problems:

  1. If we have a table visualization in which 5 measures cannot be filtered with calculation groups and 5 can already be filtered.
  2. The next 5 measures in the table are measures of difference in value: Measure 1 - Measure 2
4

@Harsh please help :slight_smile:

5

Hello @Harris,

Firstly, while posting questions onto the Community Forum please try to provide either the working or demo PBIX file so that members of the forum can provide assistance in a better and efficient manner. For poster, it can be a general query but for members who’re trying to assist or answer, for them it’s not.

By not attaching the reference files, members have to create data and necessary files on their own based on their judgement which involves guessing work and un-necessarily wastes their time.

Also the solution which was provided by me was based on the query/scenario which you had mentioned in your initial post of the thread. For your what-if related scenario’s which weren’t part of your original post, please create new thread and provide as clear description as possible with reference files to work on.

Please go through the Forum guidelines for your reference. If the questions doesn’t contain the appropriate descriptions and if the reference files are missing then members will refrain from posting solutions onto that thread and poster will not receive solutions in a timely manner. Ensure that while posting questions all the forum guidelines are met and adhered to.

Thanks and Warm Regards,
Harsh

3 Likes