cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Yggdrasill Member
Member

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User
Super User

Re: Count based on multiple criteria

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

 

 

 

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

6 REPLIES 6
Highlighted
Super User
Super User

Re: Count based on multiple criteria

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

 

 

 

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

Yggdrasill Member
Member

Re: Count based on multiple criteria

You absolute beauty !

Thank you !

Yggdrasill Member
Member

Re: Count based on multiple criteria

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 

Super User
Super User

Re: Count based on multiple criteria

@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

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

Yggdrasill Member
Member

Re: Count based on multiple criteria

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

Super User
Super User

Re: Count based on multiple criteria

Hi @Yggdrasill 

 

are you able to post some sample data with your date table included?

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!