topic Re: I want to count and reflect the number of reason codes against each row in DAX Commands and Tips

I want to count and reflect the number of reason codes against each row

Anonymous — Thu, 01 Aug 2019 11:28:03 GMT

Below is the table and count of Reason code is the result I am trying to get in power BI. In first coloumn same Document number is there and against each document either there is reason code present or not present . I want to count and reflect the number of reason codes against each row .

For eg. 123 document has total of 3 reason codes. so 3 count will reflect against each 123 row.

my excel formula to claculate count of reason code is :

Docs	Reason Code	Reason Code (Y/N)	Count of Reason code
123	x	1	3
123	0	0	3
123	x	1	3
123	x	1	3
231	0	0	1
231	x	1	1
231	0	0	1
231	0	0	1

I tried below DAX formula but it is giving me the total of all reason codes. But I want sum of Reason code come against each document =

call = CALCULATE(COUNTAX(Sheet1,Sheet1[Order Number]),FILTER(Sheet1,Sheet1[reasoncode1]>0))

Re: I want to count and reflect the number of reason codes against each row

Zubair_Muhammad — Thu, 01 Aug 2019 12:06:46 GMT

Hi Anonymous

Try following

=
CALCULATE ( SUM ( Sheet1[reasoncode] ), ALLEXCEPT ( sheet1, sheet1[Docs] ) )

Re: I want to count and reflect the number of reason codes against each row

Anonymous — Thu, 01 Aug 2019 12:15:19 GMT

You could try this as well:

[Count of Reason Codes] =
var __oneDocVisibleAndDirectltyFiltered = HASONEFILTER( T[Docs] )
var __reasonCodeCount =
	CALCULATE(
		SUM ( T[Reason Code (T/N)] ),
		VALUES( T[Docs] ),
		ALL( T )
	)
return
	if(
		__oneDocVisibleAndDirectlyFiltered,
		__reasonCodeCount
	)

Best

Darek

Re: I want to count and reflect the number of reason codes against each row

Anonymous — Fri, 02 Aug 2019 07:46:28 GMT

This is working. Thanks dude.