Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

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
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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