Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |