Exclude rows where Group By amount is zero

[Anonymous]

Hi there,

I need to write a DAX in my report which will DistinctCount DocNo only for the records where Grouped value (Amount) is not zero. See the following scenario.

MyTables has several records and DistictCount will be 4 (which is not a desired result).

MyGroupTable (group by DocNo) has 4 records and DistictCount will only count records where amount is not zero, so result will be 2.

Any help will be highly appreciated.

Let me know for more details.

[Jihwan_Kim]

Group Table =
SUMMARIZECOLUMNS (
'Table'[DocNo],
FILTER ( VALUES ( 'Table'[DocNo] ), CALCULATE ( SUM ( 'Table'[Amount] ) <> 0 ) ),
"@Amount", SUM ( 'Table'[Amount] )
)

https://www.dropbox.com/s/75rgey6rqntyt7k/mughalia.pbix?dl=0 

[Anonymous]

Hi @Jihwan_Kim 

Thank you for your reply.

I guess we are almost there. The DAX is returning two rows and two columns. The DAX should return only 2 in this instance, because there are only two records with non-zero amount.

Could you tweak the DAX and advise please?

Thank you

[Jihwan_Kim · Accepted solution]

I was not sure whether you wanted a calculated table or a calculated measure.

There are more than a few ways in writing measures. However, without knowing the data model,  all of them might be suitable for your model, or perhaps only one of them might be suitable.

https://www.dropbox.com/s/75rgey6rqntyt7k/mughalia.pbix?dl=0 

[amitchandak]

@Anonymous , Try measure like

Measure =
var _sum = sum(Table[Amount])
return
calculate(if(_sum=0, blank(), _sum))

[Anonymous]

Thank you @amitchandak