Show only Records where Measure 1 is 1

[Seier-Alsenz]

Hi,

i have a huge table in my worksheet. i use 3 Dimensions (PersNr, Firstname and PersNo.) and 11 Measures. I just want to see the records where Measure1 is >0. The value of Measure1 depending on MonthYear.

I thought I was smart and created a dimension (Measure1Flag) - which is 1 when (Measure >0)and (0 when it 0). At Table in the Worksheet i choose the Option  "exclude zero values" from the Dimension Measure1Flag -> it does not lead to the result that i expected. It will all Measure1Flag displayed that are 1, but for every MonthYear which the Dim is 1. Despite the fact that in my sheet the MonthYear filter is active. My logic is broken.

what is the solution to the problem?

kind regards

[stevedark · Accepted solution]

Hi @Seier-Alsenz 

You can set the table to not show zero values at a table level rather than a dimension, which will remove rows when all measures give zero or null results.

So, if your Measure1 is sum(Value) you could put this into every other measure, to check that before doing the rest of the calculation:

if(sum(Value) > 0, sum(Cost), null())

If you do that on all measures only rows where Measure1 is not zero should show.

If Measure1 is a more complicated expression you may want to move it to a variable, so that if it changes you don't need to change it in every expression. You would then have:

if($(vMeasure1) > 0, sum(Cost), null())

Hope that helps.

Steve