Count only one per category even if there are multiple events

[Anonymous]

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.

[LivioLanzo]

Hi @Anonymous 

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 =
CALCULATE (
    COUNTROWS (
        SUMMARIZE (
            Data,
            Events[Event],
            Shifts[Shift]
        )
    ),
    TREATAS (
        DATATABLE (
            "event", STRING,
            "shift", STRING,
            {
                { "EventA", "EarlyShift" },
                { "EventA", "NightShift" },
                { "EventA", "LateShift" }
            }
        ),
        Events[Event],
        Shifts[Shift]
    )
)