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.
Hello!
I have the following dataset where the Time_Slice_10 represents during which 10-minute period the event occurred. The Time_Slice_10_Count then totals the number of events that happened during that time. Note: in this example, the total is grouped by Facility.
Facility | Event_Time | Time_Slice_10 | Event_Date | Time_Slice_10_Count (calculated) |
A | 2022-05-21 07:54:07 | 2022-05-21 07:50:00 | 2022-05-21 | 2 |
A | 2022-05-21 07:58:16 | 2022-05-21 07:50:00 | 2022-05-21 | 2 |
A | 2022-07-12 19:24:07 | 2022-07-12 19:20:00 | 2022-07-12 | 1 |
A | 2022-07-15 03:45:00 | 2022-07-15 03:40:00 | 2022-07-15 | 1 |
In Power BI, I am using the following Measures to calcuate the Time_Slice_10_Count:
Solved! Go to Solution.
Hi @mollycat ,
Here are the steps you can follow:
1. Create measure.
Time_Slice_10_Count (calculated) =
var _10min=MAX('Table'[Time_Slice_10]) +TIME(0,10,0)
return
COUNTX(FILTER(ALL('Table'),'Table'[Facility]=MAX('Table'[Facility])&&'Table'[Event_Time]<=_10min&&'Table'[Event_Time]>='Table'[Time_Slice_10]&&'Table'[Event_Date]=MAX('Table'[Event_Date])
),[Facility])
Measure =
MAXX(FILTER(ALL('Table'),'Table'[Facility]=MAX('Table'[Facility])&&'Table'[Event_Date]=MAX('Table'[Event_Date])),[Time_Slice_10_Count (calculated)])
2. Result:
If you need pbix, please click here.
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Hi @mollycat ,
Here are the steps you can follow:
1. Create measure.
Time_Slice_10_Count (calculated) =
var _10min=MAX('Table'[Time_Slice_10]) +TIME(0,10,0)
return
COUNTX(FILTER(ALL('Table'),'Table'[Facility]=MAX('Table'[Facility])&&'Table'[Event_Time]<=_10min&&'Table'[Event_Time]>='Table'[Time_Slice_10]&&'Table'[Event_Date]=MAX('Table'[Event_Date])
),[Facility])
Measure =
MAXX(FILTER(ALL('Table'),'Table'[Facility]=MAX('Table'[Facility])&&'Table'[Event_Date]=MAX('Table'[Event_Date])),[Time_Slice_10_Count (calculated)])
2. Result:
If you need pbix, please click here.
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Calculate(Sumx(Summarize(Table, Table[Facility], Table[Event_Date]), Calculate(Lastnonblankvalue(Table[Event_Date]), Count(Table[Event_Date]) ) ) , allexcept(Table, Table[Facility], Table[Event_Date]) )
or check if this can help
Hi @amitchandak, thank you for your response. Unfortunately this formula is still giving me the SUM of the values, rather than the MAX. I've written this measure which seems to get me pretty close but is also not completely working:
Facility | Event_Date | Time_Slice_10 | Measure |
A | 2022-07-19 | 2022-07-19 20:00:00 | 1 |
A | 2022-07-19 | 2022-07-19 05:10:00 | 4 |
A | 2022-07-19 | 2022-07-19 05:50:00 | 1 |
A | 2022-07-19 | 2022-07-19 06:40:00 | 11 |
A | 2022-07-19 | 2022-07-19 07:10:00 | 2 |
Based on the data in the table this Measure is calculating correctly (ie: there are 11 rows of events that fall into the 2022-07-19 06:40:00 time slice, two events in the 2022-07-19 07:10:00 time slice, etc.). However, when I remove the Time_Slice_10 column, I expect the Measure to recalculate based on the columns in the table to show me the MAX value of 11. Instead it does the sum of these and returns 19 (1+4+1+11+2):
Facility | Event_Date | Measure |
A | 2022-07-19 | 19 |
Again, I really appreciate your help and response on this! 🙂
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 |