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   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 ...
  • 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:

     

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)