Showing results for 
Search instead for 
Did you mean: 
nico_koehler Frequent Visitor
Frequent Visitor

Count only one per category even if there are multiple events

Hey guys, I have a table like this:


| Timestamp |     | Day |      | Event |     | Shift |


|  14:00:00    | 01/03/2019 | Event C | Early Shift

|  13:03:00    | 01/03/2019 | Event B | Early Shift

|  08:34:24    | 01/03/2019 | Event A | Early Shift

|  15:12:21    | 01/03/2019 | Event A | Late Shift

|  24:02:12    | 01/03/2019 | Event A | Night Shift

|  03:03:11    | 01/03/2019 | Event A | Night Shift


So here is an explanation of what I need to do: every shift needs to do one "Event A" 

So in this case it would be 100% completion for the day because early, late and night shift did an "Event A"

I want to count if there is an Event A in a shift. In my example table there are 2 Event A's in Night Shift but in order for it to be completed they only need one. So I want it to be counted as completed no matter if there are 1 or 5 of these Event A's.

And it should be counted in a way so that it is scalable. That means I need to be able to look at the last few days and see if everything got 100% completed or not.


Let me know if you need more clearification on what I want to do..


Thanks for your help guys.

Super User
Super User

Re: Count only one per category even if there are multiple events

Hi @nico_koehler 


try to normalize the data and adding 2 dimensions for shifts and events





then drop the day on the matrix rows and this measure, when it is equal to 3 then 100% is completed:


Measure =
        SUMMARIZE (
        DATATABLE (
            "event", STRING,
            "shift", STRING,
                { "EventA", "EarlyShift" },
                { "EventA", "NightShift" },
                { "EventA", "LateShift" }



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

Proud to be a Datanaut!