Exclude rows from table where value = 0

[rchiew604]

My goal is to suppress rows where 'Remaining' is zero and only shows rows with value greater than zero (in this case, Remaining = 22):

Expression for 'Remaining' measure is:
if(MAXUSES - Sum(TIMES_USED)<0, null(), MAXUSES - Sum(TIMES_USED))

Unchecked 'Included null values' for both 'Customer ID' & 'Package Name' dimension but still shows all rows.

Also unchecked 'include zero values' under Add-On > Data handling but still with the same results.

Any guidance would be much appreciated.

[Ivan_Bozov]

You expression says <0, should be =0, right?

[NitinK7]

Hi,

can you attach here some sample data ?

[Vegar]

It's not the Remaining column that iscausing you trouble, it is your Sum(TIMES_USED) column. 

Suppress Zero will suppress rows where all expressions are zero. So if you make your Sum(TIMES_USED) column to return 0 when Remaining is zero then your suppression of zeros will work. 

[rchiew604]

So I changed 'Max Uses' & 'Times Used' to the following to mirror 'Remaining':

Max Uses = if(MAXUSES - Sum(TIMES_USED)=0, 0, MAXUSES)

Times Used = if(MAXUSES - Sum(TIMES_USED)=0, 0, Sum(TIMES_USED))

Now it becomes this:

Checked to make sure the 'Included zero values' option is unchecked but those zero lines are still there.

Any idea?

[Vegar · Accepted solution]

It look slike you have two additional measures In the picture you attached - [Data Sold] and [Data Expires] these expressions do return values and are thereby blocking your zero suppression. 

[rchiew604]

Yes that works. Thanks!