Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I have a data that looks like this
Date | Time | Text | Sum
1-1-2001 | 12:23:34 | a| 2
1-1-2001 | 12:23:34 | b| 3
1-1-2001 | 12:25:34 | a| 2
1-1-2001 | 12:26:34 | b| 2
1-1-2001 | 13:23:34 | a| 1
1-1-2001 | 13:24:34 | a| 2
I need a result with something like this
Text | Sum
a | 4
b | 5
a | 3
or even just the sum column.
The first and third row are grouped because they are within a 5 minute range. Similarly with 2, 4 and 5,6. My end goal is to determine the frequency of each sum value in the data. It is a large dataset with multiple text values and date values.
Hi @prakhar,
Based on my understanding, you need to sum the column [Sum] for each [Text] type every 5 minutes, right?
In this scenario, please create some calculated columns, referring to below DAX formulas:
Min Time for each text = CALCULATE ( MIN ( 'SubTotal Column'[Time] ), ALLEXCEPT ( 'SubTotal Column', 'SubTotal Column'[Date], 'SubTotal Column'[Text] ) ) Difference Minute = HOUR ( 'SubTotal Column'[Min Time for each text] - 'SubTotal Column'[Time] ) * 60 + MINUTE ( 'SubTotal Column'[Time] - 'SubTotal Column'[Min Time for each text] ) + SECOND ( 'SubTotal Column'[Time] - 'SubTotal Column'[Min Time for each text] ) / 60 Interval(every 5 minute) = INT ( 'SubTotal Column'[Difference Minute] / 5 ) Total Sum = CALCULATE ( SUM ( 'SubTotal Column'[Sum] ), ALLEXCEPT ( 'SubTotal Column', 'SubTotal Column'[Date], 'SubTotal Column'[Text], 'SubTotal Column'[Interval(every 5 minute)] ) )
Data view
Table visual
Best regards,
Yuliana Gu
I was able to create what I needed using your idea of adjusted time. I did this:
Adjusted Time = CALCULATE(
MIN('items'[Time]),
ALL('items'[Date])
ALL('items'),
HOUR('items'[Time]-EARLIER('items'[Time]))*60
+MINUTE('items'[Time]-EARLIER('items'[Time]))<=5)
Hi @v-yulgu-msft,
Thanks for the reply. That is not exactly what I need.
I was also thinking of a similar solution. But with this solution, the time is divided into fixed 5 minute intervals. I don't want to divide the time. I want to put in an if condition that is based on whether the rows that need to be grouped are within 5 minutes of each other or not. eg. by dividing into fixed intervals, 12:20 and 12:24 text are grouped and 12:29 and 12:31 won't be grouped. I need both the groupings to occur
What I think would be better is to compare rows and if two rows with same Text are within 5 minutes of each other, then add their sum values. I am not sure how this could be implemented in Power BI. I saw EARLIER function used for such cases but haven't been able to write query using them or even understand them properly.
Regards
Prakhar
User | Count |
---|---|
93 | |
84 | |
78 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |