topic Re: Count group by with condition in DAX Commands and Tips

Count group by with condition

SimDam — Tue, 17 Jan 2023 06:14:00 GMT

Hi,

Considering the below table

Order	Period	Item	Test
100	7/1/2022	1	0
100	7/1/2022	2	0
100	7/1/2022	3	1
100	8/1/2022	1	0
100	8/1/2022	2	0
100	8/1/2022	3	0
100	9/1/2022	1	1
100	9/1/2022	2	1
100	9/1/2022	3	0
200	7/1/2022	10	1
200	7/1/2022	20	1
200	8/1/2022	10	0
200	8/1/2022	20	0

I need to count if I have at least one Item with Test =1 for each Period by Order.

The result should be:

Order	Test Count
100	2
200	1

For the Order 100, only for the period 7/1/2022 and 9/1/2022 I have at least one Item with Test=1 and for the Order 200, only the period 7/1/2022 has at least one Item with Test=1 (in this case both items are fulfulling the condition but anyhow I just need at least one to fulfill the condition to count that period in the final result).

How to write a measure for "Test Count"?

Thank you

Re: Count group by with condition

FreemanZ — Tue, 17 Jan 2023 07:04:14 GMT

hi SimDam

try to write the measure like this:

TestCount = VAR _table = ADDCOLUMNS( VALUES(TableName[Period]), "Test", CALCULATE(MAX(TableName[Test])) ) RETURN COUNTROWS(FILTER(_table, [Test]=1))

it worked like this:

Re: Count group by with condition

FreemanZ — Tue, 17 Jan 2023 07:14:03 GMT

you may also write like this:

TestCount2 = VAR _table = CALCULATETABLE( TableName, TableName[Test]=1 ) VAR _table1 = SUMMARIZE( _table, TableName[Period] ) RETURN COUNTROWS(_table1)

TestCount3 = VAR _table = FILTER( TableName, TableName[Test]=1 ) VAR _table1 = SUMMARIZE( _table, TableName[Period] ) RETURN COUNTROWS(_table1)

Re: Count group by with condition

SimDam — Tue, 17 Jan 2023 07:29:37 GMT

This is already working so I will go for it.

Thanks!