Forum Discussion

tmjones2's avatar
tmjones2
Icon for Helper I rankHelper I
5 years ago

How to Make a Measure Ignore Filters in One Field

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.

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    tmjones2 removefilters('Fact'[RiskRegionKeyDim])

     

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

    • tmjones2's avatar
      tmjones2
      Icon for Helper I rankHelper I
      5 years ago

      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.

       

       

  • CNENFRNL's avatar
    CNENFRNL
    Icon for Community Champion rankCommunity Champion
    5 years ago

    Hi, tmjones2 , it's fairly straightforward,

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

or equally

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