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.
I need help in acheving the below result.
Travel Date Travel Time
4/14/2017 4:41:57 PM
4/18/2017 4:21:28 PM
4/18/2017 4:21:58 PM
4/19/2017 4:28:42 PM
4/19/2017 8:41:37 AM
4/20/2017 1:07:28 PM
4/20/2017 1:18:06 PM
4/20/2017 1:35:08 PM
4/20/2017 7:59:33 AM
4/21/2017 12:11:07 PM
4/21/2017 2:12:59 PM
4/21/2017 3:32:05 PM
4/21/2017 8:09:19 AM
4/25/2017 1:44:10 PM
4/25/2017 8:35:24 AM
4/25/2017 9:42:11 AM
4/26/2017 1:15:20 PM
4/26/2017 1:30:00 PM
4/26/2017 1:50:09 PM
4/26/2017 12:17:23 PM
4/26/2017 2:00:59 PM
4/26/2017 2:18:38 PM
4/26/2017 2:43:31 PM
4/27/2017 11:08:30 AM
4/27/2017 9:11:28 AM
then Minimum values of the result.
Travel date Travel Time
4/14/2017 16:41:57
4/18/2017 16:21:28
4/19/2017 8:41:37
4/20/2017 7:59:33
4/21/2017 8:09:19
4/25/2017 8:35:24
4/26/2017 12:17:23
4/27/2017 9:11:28
Then average should be 10:59:46
Can you please help me in acheiving this.
Solved! Go to Solution.
Sorry, couple of issues, mainly forgot my RETURN statement:
Measure = VAR __tmpTable = SUMMARIZE('Table',[Travel Date],"__Min",MIN([Travel Time])) RETURN IF(HASONEVALUE([Travel Date]),MIN([Travel Time]),AVERAGEX(__tmpTable,[__Min]))
Here is the actual one that I have in my PBIX:
MyMeasure = VAR __tmpTable = SUMMARIZE('Table7',[Travel Date],"__Min",MIN('Table7'[Travel Time])) RETURN IF(HASONEVALUE(Table7[Travel Date]),MIN([Travel Time]),AVERAGEX(__tmpTable,[__Min]))
OK, a few different issues here. One is dealing with aggregating time/duration. https://community.powerbi.com/t5/Community-Blog/Aggregating-Duration-Time/ba-p/22486
Then you have a measure totals issue. This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376
So, what you are going to need essentially is a measure like:
Measure = VAR __tmpTable = SUMMARIZE('Table',[Travel Date],"__Min",MIN([Travel Time)) IF(HASONEVALUE([Travel Date]),MIN([Travel Time]),AVERAGEX(__tmpTable,[__Min]))
Something along those lines.
Thanks for the reply, I am getting the below error.
The expression refers to multiple column. Multiple columns cann't be converted to a scalar values
Sorry, couple of issues, mainly forgot my RETURN statement:
Measure = VAR __tmpTable = SUMMARIZE('Table',[Travel Date],"__Min",MIN([Travel Time])) RETURN IF(HASONEVALUE([Travel Date]),MIN([Travel Time]),AVERAGEX(__tmpTable,[__Min]))
Here is the actual one that I have in my PBIX:
MyMeasure = VAR __tmpTable = SUMMARIZE('Table7',[Travel Date],"__Min",MIN('Table7'[Travel Time])) RETURN IF(HASONEVALUE(Table7[Travel Date]),MIN([Travel Time]),AVERAGEX(__tmpTable,[__Min]))
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 |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |