Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
bob57
Helper IV
Helper IV

Aggregating the results of a measure.

Hello,

 

Total Load Time = DATEDIFF( MAX( Trip_Tbl[StartLoad Time]), MAX( Trip_Tbl[TripTimeOut] ), SECOND ) / 60 displays the correct values when filtered by the trip key (upper right table visual). When filtered on the driver(bottom right table) , it displays the max value, but I need the sum of the load times. I'm going to try to upload the .pbix. Very small file.
 
Thank you for your time and effort.
 
Bob

 

bob57_0-1598832331627.png

 

1 ACCEPTED SOLUTION

@bob57 - See if this is it, updated PBIX is attached below signature. 

Total Load Time by Name and Location = 
    VAR __Name = MAX('Employee_Tbl'[Name])
    VAR __Location = MAX('Locaion_Tbl'[LocName])
    VAR __Table = SUMMARIZE('Trip_Tbl',[Name],[LocName],[TripKey],"Measure",[Total Load Time])
RETURN
    SUMX(__Table,[Measure])

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

12 REPLIES 12
bob57
Helper IV
Helper IV

I see no option for attaching a .pbix. Is there a way to do so? Here are the tables:

Emp Table

bob57_0-1598832917482.png

Location Table

bob57_1-1598833003198.png

Trips Table

bob57_2-1598833103304.png

Orders Table (partial)

bob57_3-1598833200162.png

 

 

@bob57 - Most people upload a PBIX file to OneDrive or Box and share the link to it. It would be very helpful if you went that route or at least posted the tables you posted as text in a table. Thre is a great article by @ImkeF that is linked to in this post about posting data to the forums.

 

How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Thank you, Greg. I will follow your advice, read the article, and repost in a manner that is easier to work with.

Bob

@bob57 - Sorry, it's just a lot of stuff to type in order to get something to test with and it's hard to wrap my head around what is going just throught reading things. Appreciate it!

 

You know, in reading through it again for a third time, it could be a measure aggregation issue. See my blog article about that here: https://community.powerbi.com/t5/Community-Blog/Design-Pattern-Groups-and-Super-Groups/ba-p/138149

The pattern is:
MinScoreMeasure = MINX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
MaxScoreMeasure = MAXX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
AvgScoreMeasure = AVERAGEX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
etc.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

No apology necessary. Proper protocol serves a valuable purpose and I need to familiarize myself with it.

@bob57 - Well apparently I'm an idiot because it's right there in the bleeping title of the post that it's a measure aggregation issue!! 🙂

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Greg,

Here is a link to the .pbix: https://techservicesllc-my.sharepoint.com/:u:/p/bstorie/EXUWPvqERqhNkm_8s0_t7LIBMrD17YCO8KOjg7fRbFBD....

 

Should I start a new post or would you like to continue using this one? If we continue here, I'll add more background information to help you better understand what I need.

 

Thanks again,

 

Bob

@bob57 - See if this is it, updated PBIX is attached below signature. 

Total Load Time by Name and Location = 
    VAR __Name = MAX('Employee_Tbl'[Name])
    VAR __Location = MAX('Locaion_Tbl'[LocName])
    VAR __Table = SUMMARIZE('Trip_Tbl',[Name],[LocName],[TripKey],"Measure",[Total Load Time])
RETURN
    SUMX(__Table,[Measure])

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

That did the trick, Greg. Thank you for your effort and patience. Now for me to learn from this!

 

Bob

@bob57 - Sure thing man, the basic concept is that you are breaking the current information for the row that is summarized in the visual and kind of "exploding" it out to an additional granularity level, [TripKey]. You have your measure calculate whatever calculation it does for each of these rows and then you can perform an aggregation across those rows using an "X" iterator like SUMX, AVERAGEX, etc. It's not the only way to solve the problem but I have found over the years that breaking problems down using VAR's and such, manipulating the data to get exactly the table of information I want and then iterating across it provides me with more reliable results with less funky rules and obtuse DAX exceptions to deal with. Also, it makes troubleshooting easier. But, it's not the only way to do things.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Greg,

A quick follow-up, if I may. I don't see where these variables are being used in the measure. Can you elaborate?

 

VAR __Name = MAX('Employee_Tbl'[Name])
VAR __Location = MAX('Locaion_Tbl'[LocName])

 

Thanks again. This communty is an invaluable resource.

Bob

@bob57 - Let me take a look and see if I can get you what you need.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors