topic Re: How to Make a Measure Ignore Filters in One Field in DAX Commands and Tips

How to Make a Measure Ignore Filters in One Field

tmjones2 — Wed, 18 Nov 2020 14:54:57 GMT

I'm trying to get a measure to ignore all filters in one field ('Fact'[RiskRegionKeyDim]). The measure I have below works if you don't use any slicers but if you try to slice on anything it doesn't work.

ClaimsOnDifferentLossState =

CALCULATE( [TotalClaims],

FILTER(ALL('Fact'),

'Fact'[LossRegionKeyDim] = selectedvalue('Fact'[RiskRegionKeyDim]) && //Loss State is the Risk State

'Fact'[RiskRegionKeyDim] <> selectedValue('Fact'[RiskRegionKeyDim]) //We only care about Georgia Loss State records for Non-Georgia Risk States

//&& 'Fact'[DivisionKeyDim] = SELECTEDVALUE('Fact'[DivisionKeyDim]) //this makes it work with the Division slicer

) )

I'm sure this doesn't work with other filters because of my use of ALL, but I can't get it to work any other way. This actually does work if you just keep adding "&& {some dimension} = selectedvalue{some dimension}" for every field that could possibly be filtered but that is obviously not the right way to do it.

The actual description of what I'm trying to do is hard to explain but I think if I can just get a high level understanding of why this doesn't work I should be good to go. I tried using ALLEXCEPT and it was a disaster... not sure what else to do.

What's the real way to solve this?

Thank you.

Re: How to Make a Measure Ignore Filters in One Field

amitchandak — Wed, 18 Nov 2020 15:25:31 GMT

tmjones2 removefilters('Fact'[RiskRegionKeyDim])

but better to have this filter from RiskRegion table and remove it

Re: How to Make a Measure Ignore Filters in One Field

tmjones2 — Wed, 18 Nov 2020 15:40:01 GMT

I'm not exactly sure what you mean, what do I replace? Do I still need a FILTER function in there? Seems like it either throws me an error or the result is the same.

Re: How to Make a Measure Ignore Filters in One Field

CNENFRNL — Thu, 19 Nov 2020 16:15:22 GMT

Hi, tmjones2 , it's fairly straightforward,

ClaimsOnDifferentLossState = CALCULATE( [TotalClaims], ALL ('Fact'[RiskRegionKeyDim]) ) or equally ClaimsOnDifferentLossState = CALCULATE( [TotalClaims], REMOVEFILTERS('Fact'[RiskRegionKeyDim]) )