Forum Discussion

SimDam's avatar
SimDam
Icon for Helper I rankHelper I
3 years ago
Solved

Count group by with condition

Hi,

Considering the below table

 

OrderPeriod ItemTest
1007/1/2022 10
1007/1/2022 20
1007/1/2022 31
1008/1/2022 10
1008/1/2022 20
1008/1/2022 30
1009/1/2022 11
1009/1/2022 21
1009/1/2022 30
2007/1/2022 101
2007/1/2022 201
2008/1/2022 100
2008/1/2022 200

 

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

The result should be:

 

OrderTest Count
1002
2001

 

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's avatar
    FreemanZ
    3 years ago

    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:

     

3 Replies

  • FreemanZ's avatar
    FreemanZ
    Icon for Super User rankSuper User
    3 years ago

    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:

     

    • FreemanZ's avatar
      FreemanZ
      Icon for Super User rankSuper User
      3 years ago

      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)
    • SimDam's avatar
      SimDam
      Icon for Helper I rankHelper I
      3 years ago

      This is already working so I will go for it.

      Thanks!