Forum Discussion
Exclude rows where Group By amount is zero
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.
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
5 Replies
Anonymous , Try measure like
Measure =
var _sum = sum(Table[Amount])
return
calculate(if(_sum=0, blank(), _sum))Thank you amitchandak
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
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
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
