cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
VistaDee
Frequent Visitor

LEFT JOIN creating Duplicate in SUM

So I was wondering if I could get some help withi this:

 

I have 2 seperate tables:

Projects
ProjectIDProjectNameProjectCost
123456Tilling Kitchen150
545454Painting Hallway100
125877Wallpapering Bedroom200
Jobs
JobIDJobProjectID
1Buy Grout123456
2Take Measurements123456
3Bought Paper125877

 

But because I need a table that shows projects whether they have a job or not, I set up a new Merged table as below:

 

AllProjectsAndJobs
ProjectIDProjectNameProjectCostJobIDJobProjectID
123456Tiles and Tubs1501Buy Grout123456
123456Tiles and Tubs1502Take Measurements123456
545454Paint and Brushes100   
125877Wallpaper and Glue2003Bought Paper125877

 

The problem I have is that because project 123456 has 2 jobs and I want a table to just show the Project and its costs, its duplicating the value for 123456 as it has 2 job entries:

 

PowerBI.jpg

 

How do I get around this? 

 

Thanks

 

 

1 ACCEPTED SOLUTION
amitchandak
Super User IV
Super User IV

@VistaDee , You can create a join between these two assume projects has unique project only. 1-M join(No need to merge)

 

a measure like this should have give you projects with no jobs too

count(Jobs[ProjectID])+0



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

1 REPLY 1
amitchandak
Super User IV
Super User IV

@VistaDee , You can create a join between these two assume projects has unique project only. 1-M join(No need to merge)

 

a measure like this should have give you projects with no jobs too

count(Jobs[ProjectID])+0



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors