topic Re: Measure that is not affected by Filter Slicers BUT that has a filter in DAX Commands and Tips

Measure that is not affected by Filter Slicers BUT that has a filter

Anonymous — Wed, 14 Jul 2021 20:37:12 GMT

I am trying to find the right way to create a measure that is not affected by the filter slicers that are on the PBi page, but that is filtered by the year of the data.

The measure I want to creat is the sum of a whole column, filtered by all the values that are from 2021 and for now i have the following:

Total =

CALCULATE(

SUM('Table'[Column)]),

ALL('Table')

)

Which effectively makes sure that this measure is not affected by any filter slicers on the page that I want to use it.

The thing is that I want to make sure that this measure only takes the values of this year (2021) and therefore the logical thing would be to put ALL(Table[Date]) but there is a slight problem. One of the filter slicers of the page is Table[Date] by Month, or rather, one of the slicers helps show the information monthly, therefore when i use ALL(Table[Date]) the measure is affected by that filter when I do not want it to be.

I have tried with various different DAX commands but I am not able to figure it out, if anyone could help me I would greatly appreciate it!

Thank you!

Re: Measure that is not affected by Filter Slicers BUT that has a filter

wdx223_Daniel — Thu, 15 Jul 2021 00:29:07 GMT

Total =

CALCULATE(

SUM('Table'[Column)]),

ALL('Table'),Year(table[Date])=2021

)

Re: Measure that is not affected by Filter Slicers BUT that has a filter

Anonymous — Thu, 15 Jul 2021 13:49:40 GMT

I ended up doing something very similar to this, but for some reason my date column did not want to filter by year, so i simply made a column "Year" in my table. Thank you!

Re: Measure that is not affected by Filter Slicers BUT that has a filter

aausten22 — Thu, 02 Mar 2023 20:19:31 GMT

Hi There,

I have tried something similar though I am wanting to find the average of everything without the filter effecting the number but having some issues. Below is the code

P&TAverage = CALCULATE(Average('Data Set - Leave Liability'[Total Days]),All('Data Set - Leave Liability'[Branch]),'Data Set - Leave Liability'[Branch] = "Policy & Trade")