cancel
Showing results for
Did you mean:
Member

## 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 1000 A 07 16.11.2017 1000 A 2 07 17.11.2017 1000 A 07 8.1.2018 1000 A 3 07 9.1.2018 1000 A 07 12.1.2018 1000 A 4 07 1.6.2018 1000 A 5 07 3.10.2018 1000 A 6 07 4.10.2018 1000 A 07 3.1.2019 1000 A 7 07 11.2.2019 1000 A 8 07 12.2.2019 1000 A

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

## Re: Count based on multiple criteria

you can do thi sin 2 steps:

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

and then this final column:

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

Proud to be a Datanaut!

6 REPLIES 6
Highlighted
Super User

## Re: Count based on multiple criteria

you can do thi sin 2 steps:

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

and then this final column:

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

Proud to be a Datanaut!

Member

## Re: Count based on multiple criteria

You absolute beauty !

Thank you !

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)

 ColumnA Date ColumnB ColumnC Calculate count Correct Calc count Day of week 7 21.9.2017 1000 A 1 1 5 7 22.9.2017 1000 A 6 7 16.11.2017 1000 A 2 2 5 7 17.11.2017 1000 A 6 Friday NEW LINE 20.11.2017 1000 A 3 2 Monday 7 8.1.2018 1000 A 4 3 2 7 9.1.2018 1000 A 3 7 12.1.2018 1000 A 5 4 6 7 1.6.2018 1000 A 6 5 6 7 3.10.2018 1000 A 7 6 4 7 4.10.2018 1000 A 5 7 3.1.2019 1000 A 8 7 5 7 11.2.2019 1000 A 9 8 2 7 12.2.2019 1000 A 3

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

Super User

## Re: Count based on multiple criteria

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```

Proud to be a Datanaut!

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

## Re: Count based on multiple criteria

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