Forum Discussion
FILTER and SUM
Hi,
I am trying to create a measure/column with below example on PowerBI. I need the selected filter from 'activity' and its 'duration' summed up into the result which also needs to relate and be connected to the 'Name' and 'Date' column as shown in my example.
Please can you advise what would be the best option? I have tried different DAX formulas but getting error or wrong result everytime.
Thank you very much.
Hi Anonymous
Download PBIX file with the data and code below
The Data Model in Power BI doesn't have a duration data type so the times you have in your table for the Duration column will be stored as decimal numbers.
However you can still format these numbers as 'time/duration' as you have shown by using this measure
Total Time = VAR Sum_Elapsed_Time = CALCULATE(SUM('Table'[Duration]), FILTER(ALL('Table'), 'Table'[Date] = SELECTEDVALUE('Table'[Date]) && 'Table'[Name] = SELECTEDVALUE('Table'[Name]))) VAR _hrs = Sum_Elapsed_Time * 24 VAR hrs = INT(_hrs) VAR _mins = (_hrs - hrs) * 60 VAR mins = INT((_hrs - hrs) * 60) VAR secs = ROUND((_mins - mins)*60,0) RETURN FORMAT(hrs,"00") & ":" & FORMAT(mins,"00") & ":" & secswhich gives this
I've left the Duration and Result columns just so you can see how the durations are stored. These can be removed from the visual.
If you want you could also create another column to display the Duration values formatted in the same way that the Total Time column is.
Regards
Phil
3 Replies
- PhilipTreacy
Super User
Hi Anonymous
Download PBIX file with the data and code below
The Data Model in Power BI doesn't have a duration data type so the times you have in your table for the Duration column will be stored as decimal numbers.
However you can still format these numbers as 'time/duration' as you have shown by using this measure
Total Time = VAR Sum_Elapsed_Time = CALCULATE(SUM('Table'[Duration]), FILTER(ALL('Table'), 'Table'[Date] = SELECTEDVALUE('Table'[Date]) && 'Table'[Name] = SELECTEDVALUE('Table'[Name]))) VAR _hrs = Sum_Elapsed_Time * 24 VAR hrs = INT(_hrs) VAR _mins = (_hrs - hrs) * 60 VAR mins = INT((_hrs - hrs) * 60) VAR secs = ROUND((_mins - mins)*60,0) RETURN FORMAT(hrs,"00") & ":" & FORMAT(mins,"00") & ":" & secswhich gives this
I've left the Duration and Result columns just so you can see how the durations are stored. These can be removed from the visual.
If you want you could also create another column to display the Duration values formatted in the same way that the Total Time column is.
Regards
Phil