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
Sean2
Helper I
Helper I

Help with relationships and/or merging queries

Hello. I have two issues when trying to work with my data and I’m looking for help.

 

One, since the labor hours and labor cost from the “All Tasks” data is rounded, I want to use the more precise “Hours (From Labor Log)” and “Labor Cost (From Labor Log)” data from “All Labor Log” query instead. I then want to combine the labor cost (from the Labor Log data) and equipment cost (from the All Tasks data) to get a Total cost. I have tried merging those queries but I ended up getting wrong numbers that way.

My second issue is I’d like to be able to filter the tasks for a page/visual to only see tasks that had any labor from a certain team.

I’m not sure I’ve set up my relationships to be able to accomplish all this, and when I’ve tried to merge the data I end up getting highly inflated numbers.

 

Any help would be great!

 

My PBX file:

https://drive.google.com/drive/folders/1nByRHwkPuWEOtKXzV84tpOHAin5idOHQ?usp=sharing

 

1 ACCEPTED SOLUTION

Hey @Sean2

 

Just to be clear, I'm not suggesting changing the source files, just doing the data modelling in Power Query to get things to the same grain.

Have a look at the attached (updated) PBIX. Let me know if this gets you closer to what you want and if you have any questions.

 

I've added a pPath parameter so you can update that to point to the correct path for your files.

 

Have I solved your problem?
Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;).
chrome-9xf-Zagzel-B

If you found this post helpful, please give Kudos.
It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen.
If you find my signature vaguely amusing, please give Kudos.
KIfp67uy-Sr
Proud to be a Super User!PBI-Super-User-Rank-30x30-1x

View solution in original post

5 REPLIES 5
v-xiaoyan-msft
Community Support
Community Support

Hi @Sean2 ,

 

Does the replies above solve your problem? If it has been solved, please mark the correct reply as the standard answer to help the other members find it more quickly.Thanks in advance for your kind cooperation!

 

 

Hope it helps,


Community Support Team _ Caitlyn

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

KNP
Super User
Super User

Hi @Sean2

 

I've had a look at the PBIX file.

  • My first suggestion would be to get your 'All Labor Log' on the same grain as your 'All Tasks'. So I think aggregating the 'All Labor Log' to day will give you what you need.
  • At this point, you've either got the option to merge 'All Labor' with 'All Tasks' or keep them separate.
  • You will likely need a 'Task' dimension table (maybe others also) to bring the data together. 

If you provide the data files with your PBIX I'll be able to do some more with it. I can't do anything in Power Query (Transform Data) without them.

 

Your data model would end up looking more like this...

KNP_0-1644259890426.png

Hope this helps some.

 

Have I solved your problem?
Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;).
chrome-9xf-Zagzel-B

If you found this post helpful, please give Kudos.
It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen.
If you find my signature vaguely amusing, please give Kudos.
KIfp67uy-Sr
Proud to be a Super User!PBI-Super-User-Rank-30x30-1x

Thanks @KNP for taking a look! I've uploaded the CSV source data files. Note the All Tasks data has duplicate entries for tasks because there was GIS point data with the export that I stripped out, then did Remove Duplicates on the Task ID in query editor. 

Also, I can't change the source files because this is the way the data exported from the system and I want to just be able to replace those files and re-fresh the report whenever we want updated reports.

Hey @Sean2

 

Just to be clear, I'm not suggesting changing the source files, just doing the data modelling in Power Query to get things to the same grain.

Have a look at the attached (updated) PBIX. Let me know if this gets you closer to what you want and if you have any questions.

 

I've added a pPath parameter so you can update that to point to the correct path for your files.

 

Have I solved your problem?
Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;).
chrome-9xf-Zagzel-B

If you found this post helpful, please give Kudos.
It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen.
If you find my signature vaguely amusing, please give Kudos.
KIfp67uy-Sr
Proud to be a Super User!PBI-Super-User-Rank-30x30-1x

Thanks KNP! Sorry for the dealy. This definitly set me on the right course and I'm getting correct info now! This was super helpful.

The parameters for data source is a good idea, though I may have to change that to point to the SharePoint location where the source files are usually kept.

Thanks so much!!

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.