Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi there, so I receive data in date column {datetime} in the format –
28-October-2021 8:15 PM.
I get the data in 15 minute intervals 15,30,45,00 min. When the equipment is fully up and running, I receive around 60 – 100 events in the {description} column according to throughput, rejects and stoppages, all with the same time stamp as the above 28-October-2021 8:15 PM. This will happen for the next time stamp - 28-October-2021 8:30… etc etc
To rule out any maintenance testing as runtime, I only want to count each time stamp [datetime], if there is greater than 10 events within that timestamp. My current measure is
Hour Runtime = ([15m count]/4)+ 0
Hope this isn’t too confusing, and thanks in advance
If I understand you correctly, in Power Query:
Do a 'Group By' on the datetime column.
Use 2 aggregations: 1 for Count rows, 1 for All Rows.
Filter (from the column header) the Count column to be greater than 5.
Expand the 'all' column header to bring your data back
Hi @Anonymous
A few ways of doing this, one of which is as follows:
Occurence = COUNTX ( FILTER ( Table, EARLIER ( Table[Column] ) = Table[Column] ), Table[Column] )
Hope this helps 🙂
Theo
If I have posted a response that resolves your question, please accept it as a solution to formally close the post.
Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!
Want to connect?www.linkedin.com/in/theoconias
@TheoC Thank you Theo, I think I made it too confusing in my description. Basically I want to do a DISTINCTCOUNT in the datetime column to add my 15min increments (x4 for the hour) for equipment runtime, BUT exclude any with < 5 because i dont want to include in the runtime, as per the example. Can you think of a way i can do this? Thankyou again
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
129 | |
106 | |
106 | |
86 | |
72 |