cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## 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..

Solution Sage

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

Proud to be a Datanaut!