Count group by with condition

[SimDam]

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

[FreemanZ · Accepted solution]

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:

[SimDam]

This is already working so I will go for it.

Thanks!

[FreemanZ]

you may also write like this:

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

or 

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