Count using Grouping

[Harmeet Singh 2]

I am trying to create a simple row count chart with the following data source id,Status, Date

Now id is not unique and can have duplicates.

In the chart, I want to show a trend of number of unique ids, all instances of which had the same status over time.

For example

 

 

 

ID

Status

Date

 

 

1

ON

4/9/2021

 

 

1

ON

4/9/2021

 

 

2

ON

4/9/2021

 

 

2

OFF

4/9/2021

 

 

3

OFF

4/9/2021

 

 

3

OFF

4/9/2021

 

 

4

ON

4/9/2021

 

 

4

ON

4/9/2021

 

 

 

In the case above the chart should show 2 for status ON for the date. 2 is not counted because one of the statues is OFF.

[Fabian Duerr]

It's not that straight forward because you have a certain condition to be met (all ON!). Here's a solution with a calculated column that I call [CountThisRow]

CASE

WHEN Sum(If([status]="ON",1,0)) OVER ([iD],[Date])=Count([iD]) OVER ([iD],[Date]) THEN 1 / Count([iD]) OVER ([iD],[Date]) ELSE 0

ENDIt first counts the ONs per ID and Date and checks if this is equal to the number of rows for this ID and Date (are all ON). If this is true it assigns every row the value 1 divided by the number of rows per ID and Date. Else it assigns 0. Now you can plot the Sum([CountThisRow]) per Date.

[Harmeet Singh 2]

Well the problem with that approch is that the status column has many unique values. I need something more dynamic. 

[Fabian Duerr]

I see. Then create a document property (dropdown in a textbox) that holds all unique values from your status column and use this property in the case statement. By this the user can pick whatever status he wants for the aggregation.

[Harmeet Singh 2]

Thanks that works