Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello,
Solved! Go to 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])
I see no option for attaching a .pbix. Is there a way to do so? Here are the tables:
Emp Table
Location Table
Trips Table
Orders Table (partial)
@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
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.
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!! 🙂
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])
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.
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.
User | Count |
---|---|
42 | |
28 | |
23 | |
18 | |
16 |
User | Count |
---|---|
54 | |
35 | |
18 | |
18 | |
15 |