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

ColumnADateColumnBColumnCCalculate count
0721.9.20171000A1
0722.9.20171000A 
0716.11.20171000A2
0717.11.20171000A 
078.1.20181000A3
079.1.20181000A 
0712.1.20181000A4
071.6.20181000A5
073.10.20181000A6
074.10.20181000A 
073.1.20191000A7
0711.2.20191000A8
0712.2.20191000A 

 

My desired output is the Calculate count. I do not want to count the rows where the criteria meets if it has been counted for the previous day.

My effort so far is this

Calculate count =
CALCULATE (
    COUNTROWS(DemoTable);
    FILTER (
        DemoTable;
        DemoTable[ColumnB] = EARLIER ( DemoTable[ColumnB] )
            && DemoTable[Date] <= EARLIER ( DemoTable[Date] )
            && DemoTable[ColumnC] = 1000
    )
)

This returns 1 - 13 events so it is not filtering out the sequental days.

Any help here would be greatly appreciated.

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] )
    )
)

     

     

     

6 Replies

  • 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

    Going back to this problem.

     

    I have defined a holiday table which is merged to my date table. I want to exclude holidays, saturdays and sundays from my counter but doesn't the function EARLIER() expect consecutive dates ?

    Example of the output (Correct Calc count)

    ColumnADateColumnBColumnCCalculate count Correct Calc countDay of week 
    721.9.20171000A1 15 
    722.9.20171000A   6 
    716.11.20171000A2 25 
    717.11.20171000A   6Friday
    NEW LINE20.11.20171000A3  2Monday
    78.1.20181000A4 32 
    79.1.20181000A   3 
    712.1.20181000A5 46 
    71.6.20181000A6 56 
    73.10.20181000A7 64 
    74.10.20181000A   5 
    73.1.20191000A8 75 
    711.2.20191000A9 82 
    712.2.20191000A   3 

     

    I tried to exclude the holidays and weekends from my calculations but I'm not getting the desired "correct" output 

    • LivioLanzo's avatar
      LivioLanzo
      Solution Sage
      7 years ago

      Yggdrasill 

       

      I think you just need to change the 'AddCol1' formula to:

       

      AddedCol1 = 
var subDays = IF( Data[Day of Week] = 2, 3, 1 )
return
ISEMPTY(
    CALCULATETABLE(
        Data,
        ALLEXCEPT( Data, Data[ColumnB], Data[ColumnC] ),
        Data[Date] =  EARLIER( Data[Date] ) - subDays
    )
) + 0
      • Yggdrasill's avatar
        Yggdrasill
        Responsive Resident
        7 years ago

        That could work well in case of mondays. However, I'm dealing with holidays too !

        I have a Holiday table merged to my datetable so all holiday rows are marked with 0 and 1 if their active. I basically need to count the days between the last active day and current active day and use that variable for the subDays