exclude entire set if one item has predifined value

[kdm52rus]

Hello!

Here is what i got:

I have a list of items, dates and states. i want that if some dimension got state with some predefined value then all values got excluded.

So in example above dimension2 got 0 and i want to exclude everything(0, 2 and 1) because there is a zero in set.

Can i somehow achieve that? (via set analysis?)

Thanks in advance!

[sunny_talwar · Accepted solution]

You might need to use Aggr() function here

Sum(Aggr(If(Count({<dimension2 = {0}>} dimension2) = 0, Sum(Measure)), dimension0, dimension1, dimension2))

[rubenmarin]

Or maybe:

Sum(Aggr(If(Count(TOTAL <dimension0, dimension1> {<dimension2= {'0'}>} dimension2) = 0, Sum(measure)), dimension0, dimension1))

[kdm52rus]

Thanks! Thats exactly what i need.

[kdm52rus]

Thank you for your answer! Unfortunately i can mark only one reply as answer =| Marked as helpful though.