October 28 & 29: Experts share their secrets on how to pass the Fabric Analytics Engineer certification exam—live. Learn more
I have 2 columns start and stop I want to count the number of start stop events according to the following rules
counting starts when first discharge occurs
• any start in the first 12 hour block is counted as 1 event
• any new start in the next and subsequent 24 hour blocks are each counted as 1 additional event
• this counting continues until there is a 24 hour block with no event.
• for the next event after 24 hour block with no event, the sequence begins again
Could anyone help me please
Thank you in advance
Hi @kenblack
Besides, i will give an example, please help me to understand your requirements:
Please correct me if i misunderstand something.
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@kenblack , You can get diff with min date time of block and use that to get id
diff with min date time block
new col= datediff( minx(filter(Table, [block] =earlier([block]) ),[date time]),[date time],hours)
Hi @kenblack ,
Can you please share some sample data to suggest a solutions for your query?
Thanks,
Pragati
This should now work Pragatti11
Thanks Pragati - here is my data
Hi @kenblack
My account has no aceess yo your file.
It is not recommended to share personal files/data into one drive personal.
If your file contains important and privacy data, please remove it.
Best Regards
Maggie
Hello Pragati 11 sorry to be a pain but i have tried to see how I can share a sample with you would you mind guiding me through that as well
Really sorry but thank you
Hi @kenblack
You could upload a excel file to one drive for bussiness, then share the url with us.
It is better to make the a small sample with expected result.
Best Regards
Maggie
@kenblack you can share using one drive/google drive.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Thanks Parry here is my data
User | Count |
---|---|
105 | |
99 | |
98 | |
86 | |
49 |
User | Count |
---|---|
162 | |
142 | |
132 | |
102 | |
63 |