Forum Discussion

Yggdrasill's avatar
Yggdrasill
Responsive Resident
7 years ago
Solved

Count based on multiple criteria

I have this demotable. ColumnB has many different values, as well as Column C. Disregard ColumnA ColumnA Date ColumnB ColumnC Calculate count 07 21.9.2017 1000 A 1 07 22.9.2017 10...
Need Help
  • LivioLanzo's avatar
    LivioLanzo
    7 years ago

    Hi Yggdrasill

     

    you can do thi sin 2 steps:

     

    first add this column:

     

     

    AddedCol1 = 
ISEMPTY(
    CALCULATETABLE(
        Data,
        ALLEXCEPT( Data, Data[ColumnB], Data[ColumnC] ),
        Data[Date] = EARLIER( Data[Date] ) - 1 
    )
) + 0

     

    and then this final column:

     

     

    AddedCol2 = 
IF(
    Data[AddedCol1] = 0,
    BLANK(),
    CALCULATE(
        SUM( Data[AddedCol1] ),
        ALLEXCEPT( Data, Data[ColumnC], Data[ColumnB] ),
        Data[Date] <= EARLIER( Data[Date] )
    )
)

     

     

     

LivioLanzo's avatar
LivioLanzo
Solution Sage
7 years ago

Hi Yggdrasill

 

you can do thi sin 2 steps:

 

first add this column:

 

 

AddedCol1 = 
ISEMPTY(
    CALCULATETABLE(
        Data,
        ALLEXCEPT( Data, Data[ColumnB], Data[ColumnC] ),
        Data[Date] = EARLIER( Data[Date] ) - 1 
    )
) + 0

 

and then this final column:

 

 

AddedCol2 = 
IF(
    Data[AddedCol1] = 0,
    BLANK(),
    CALCULATE(
        SUM( Data[AddedCol1] ),
        ALLEXCEPT( Data, Data[ColumnC], Data[ColumnB] ),
        Data[Date] <= EARLIER( Data[Date] )
    )
)

 

 

 

  • Yggdrasill's avatar
    Yggdrasill
    Responsive Resident
    7 years ago

    You absolute beauty !

    Thank you !