Forum Discussion
AVERAGE USER PER 30 MINUTE INTERVAL THAT CAN BE FILTERED BY MULTIPLE COLUMNS
Hi Everyone! I tried creating an average users per status per interval however I can create the average for overall but cannot be filtered by groupings. Sharing a sample raw and dax that I've created:
RAW TABLE:
| GROUP | NAME | STATUS START DATE/TIME | STATUS END DATE/TIME | STATUS | 30min Interval_START | 30min Interval_END | date_time_interval_start | date_time_interval_end | |
| Group1 | User240 | 06/24/2025 10:33:15 | 06/24/2025 10:34:10 | Busy | 10:30:00 | 10:30:00 | 06/24/2025 10:30:00 | 06/24/2025 10:30:00 | |
| Group1 | User240 | 06/24/2025 08:34:30 | 06/24/2025 08:41:00 | Busy | 08:30:00 | 08:30:00 | 06/24/2025 08:30:00 | 06/24/2025 08:30:00 | |
| Group1 | User240 | 06/24/2025 08:26:32 | 06/24/2025 08:34:28 | Busy | 08:00:00 | 08:30:00 | 06/24/2025 08:00:00 | 06/24/2025 08:30:00 | |
| Group1 | User239 | 06/24/2025 15:00:00 | 06/24/2025 15:14:41 | Break | 15:00:00 | 15:00:00 | 06/24/2025 15:00:00 | 06/24/2025 15:00:00 | |
| Group1 | User93 | 06/24/2025 14:30:35 | 06/24/2025 14:45:56 | Break | 14:30:00 | 14:30:00 | 06/24/2025 14:30:00 | 06/24/2025 14:30:00 | |
| Group1 | User149 | 06/24/2025 15:15:06 | 06/24/2025 15:30:38 | Break | 15:00:00 | 15:30:00 | 06/24/2025 15:00:00 | 06/24/2025 15:30:00 | |
| Group1 | User149 | 06/24/2025 14:46:01 | 06/24/2025 14:51:03 | Break | 14:30:00 | 14:30:00 | 06/24/2025 14:30:00 | 06/24/2025 14:30:00 | |
| Group1 | User149 | 06/24/2025 09:37:11 | 06/24/2025 09:52:13 | Break | 09:30:00 | 09:30:00 | 06/24/2025 09:30:00 | 06/24/2025 09:30:00 | |
| Group14 | User105 | 06/24/2025 11:22:57 | 06/24/2025 14:01:42 | Online | 11:00:00 | 14:00:00 | 06/24/2025 11:00:00 | 06/24/2025 14:00:00 | |
| Group14 | User105 | 06/24/2025 08:58:19 | 06/24/2025 11:01:29 | Online | 08:30:00 | 11:00:00 | 06/24/2025 08:30:00 | 06/24/2025 11:00:00 | |
| Group14 | User105 | 06/24/2025 07:03:23 | 06/24/2025 08:37:16 | Online | 07:00:00 | 08:30:00 | 06/24/2025 07:00:00 | 06/24/2025 08:30:00 | |
| Group14 | User105 | 06/24/2025 06:02:30 | 06/24/2025 06:56:39 | Online | 06:00:00 | 06:30:00 | 06/24/2025 06:00:00 | 06/24/2025 06:30:00 |
INTERVAL TABLE:
Added Online, Busy, Break Column:
Added Online, Busy and Break Measures:
GOAL:
Column= Status
ROW= 30min Interval
VALUES = average users during the interval per status (meaning it should also consider the in between of start time and end time of status) example: if the user is online from 1:30:00 to 2:30:00, he should also appear on interval 2:00:00)
Thank you in advance
Hi Orman01 ,
Thanks for the follow-up and apologies for the delay.
Attached the updated .pbix file.
Please revert back if it doesn't meet your needs or for any further adjustments.Thank you.
20 Replies
- FBergamaschi
Super User
Please include, in a usable format, not an image, a small set of rows for each of the tables involved in your request and show the data model in a picture, so that we can import the tables in Power BI and reproduce the data model. The subset of rows you provide, even is just a subset of the original tables, must cover your issue or question completely. Do not include sensitive information and do not include anything that is unrelated to the issue or question. Please show the expected outcome based on the sample data you provided and make sure, in case you show a Power BI visual, to clarify the columns used in the grouping sections of the visual.
Need help uploading data? click here
Want faster answers? click here
Thanks for pointing that out. Updated the post. Let me know if the updated one will do. Thank you.
Hi Orman01 ,
Thanks for reaching out to Microsoft Fabric Community and sharing the details.I tried to reproduce using the RAW data you provided, but couldn’t match the expected results.
If possible, please share a sample PBIX file(without any sensitive data) along with the expected output image. That would allow us to better understand the model structure, relationships, and measures used, and assist you more effectively.
You can upload PBIX to your OneDrive or GoogleDrive and share the link here with permissions to edit.
Please follow this guide to share sample data: How to provide sample data in the Power BI Forum - Microsoft Fabric Community
Or if you found a solution, please consider sharing it here to help others who have similar requirement.
Thank you.
Hi Orman01 ,
We’re following up on your query and would be happy to assist further if needed. To help us provide more accurate support, please consider sharing a sample .pbix file with all sensitive data removed.
Thank you.Hi Orman01 ,
We wanted to kindly follow up regarding your query. If you need any further assistance, please reach out. To help us provide more accurate support, please consider sharing a sample .pbix file with all sensitive data removed. Or, if you have found solution elsewhere, please share here so that others with similar scenario would benefit
Thank you.Hi Orman01 ,
Just following up on your query. If you need any further assistance, please reach out. To help us provide more accurate support, please consider sharing a sample .pbix file with all sensitive data removed. Or, if you have found solution elsewhere, please share here so that others with similar scenario would benefit
Thank you.Hi Apologies for late response as I can't find a way to upload sample files.
Hi Orman01 ,
Thanks for the update.You can upload sample files or PBIX to your OneDrive or GoogleDrive and share the link here with permissions to edit.
Please follow this guide to share sample data: How to provide sample data in the Power BI Forum - Microsoft Fabric Community
