How to remove qty when associated qty = 0

[davyqliks]

Hi All,

I have the following table:

2 of the fields are measures as below:

=If(isnull(ITM01),[Purchase Order No]) - This only includes the values in the table if there is no itm01

=sum(aggr(sum( distinct [Ticket Qty]),[Purchase Order No],[File Name],[Ticket Qty])) - This sums the multiple qty per file name to give a single value.

My Issue.

I have to remove from the table All matching ( all lines in the image) Purchase order number if any of the total qty = 0.

so this example should not show in the table.

I have no idea how to approach this.

Can anyone set me in the right direction?

I essentially need to search PO for total qty 0 and if found, remove all matching Purchase Order Number.

Any help or advise would be very appreciated here.

thank you

I 

[BrunPierre]

=Sum(Aggr(Sum({<[Purchase Order No]={"=Sum(TOTAL [Ticket Qty])>0"}>}DISTINCT [Ticket Qty]),[Purchase Order No],[File Name],[Ticket Qty]))

?

[davyqliks]

Hi 

BrunPierre,

PREVIEW

Thank you for taking the time to share this, i have tried as below:

Added the following as a measure in the QTY field.

=Sum(Aggr(Sum({<[Purchase Order No]={"=Sum(TOTAL [Ticket Qty])>0"}>}DISTINCT [Ticket Qty]),[Purchase Order No],[File Name],[Ticket Qty]))

And the result is the same.

I feel like i need to make the values Null where there is a 0 associated to a purchase order (as one of the qty) and then aggr the measure to use as a dimension and remove nulls. but i cannot work out how to achieve this.

Thank you, any other options or ideas would be much appreciated.

thank you

Daniel

[BrunPierre]

What's the expression behind the total column? And as your expected result, do you want to exclude the rows when total = 0?

[davyqliks]

I used the expression you gave as the total column.

The expected result ( i guess)

Is 

If for any of the same purchase order number, the total qty is 0. make null for all lines and then do not show null values so all associated qty for a PO are not showing ( if any are 0)... so for this example, i do not wan these lines in the table (null)

Thanks so much.

Daniel

[BrunPierre · Accepted solution]

@davyqliks Maybe this

Count({<[Purchase Order No]={"=Min(Total)>0"} >}[File Name]

?