Skip to main content
cancel
Showing results for 
Search instead 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

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

 

Thanks for your help guys.

1 REPLY 1
LivioLanzo
Solution Sage
Solution Sage

Hi @Anonymous 

 

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

 

2019-03-01_12-27-49.jpg

 

 

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

 


 


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


Proud to be a Datanaut!  

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.