cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
darko861
Helper II
Helper II

Dax Measure with if statement and columns from different tables

Hi community,

 

I have the following visual:

darko861_0-1618992265477.png

I want to add a measure that calculates the difference like this:

Diff = SUM(v_rpt_apticcustom_Employee_Time_Billingv6[Worked Hours]) -SUM('apticproject view_workedtime'[Worked.1])
 
Unfortunately, it created a lot of duplicates:
 
darko861_1-1618992719110.png

 

Both columns Worked.1 and Worked Hours come from two different tables that are connected through a relationship. Is there another way to add a difference column in the Visual that keeps the same layout as the first picture, and does not create duplicates of the users?

 

The table 'apticproject view_workedtime' looks like this:

 

darko861_2-1618993629844.png

 

the table v_rpt_apticcustom_Employee_Time_Billingv6 looks like this:

 

darko861_3-1618993848050.png

 

 

My end goal is to create another column in the visual that uses a measure and looks something like this =IF([Diff]>=0,[[Worked Hours]],[[Worked.1]]). How can I write this measure properly? Or should a calculated column be used instead of a measure?

2 ACCEPTED SOLUTIONS

Hi @amitchandak I managed to solve the issue, I had a department column that was causing the duplications, so I removed that. The department column was from the employee table but it was not connected to any other table.

 

darko861_0-1619000069522.png

looks much better now:

darko861_1-1619000435985.png

 

Now I want another column in the visual that uses a measure and looks something like this =IF([Diff]>=0,[Worked Hours],[Worked.1]). How can I write this measure properly? Or should a calculated column be used instead of a measure?

View solution in original post

This measure worked for me:

Worked Hours2 = IF([Diff]>=0,SUM(v_rpt_apticcustom_Employee_Time_Billingv6[Registered_Hours]),SUM('apticproject view_workedtime'[Worked.1]))

View solution in original post

4 REPLIES 4
amitchandak
Super User
Super User

@darko861 , How these tables are joined. From where the Employee name column (first column) is coming.

is it Many -Many ?

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

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

Hi @amitchandak I managed to solve the issue, I had a department column that was causing the duplications, so I removed that. The department column was from the employee table but it was not connected to any other table.

 

darko861_0-1619000069522.png

looks much better now:

darko861_1-1619000435985.png

 

Now I want another column in the visual that uses a measure and looks something like this =IF([Diff]>=0,[Worked Hours],[Worked.1]). How can I write this measure properly? Or should a calculated column be used instead of a measure?

View solution in original post

This measure worked for me:

Worked Hours2 = IF([Diff]>=0,SUM(v_rpt_apticcustom_Employee_Time_Billingv6[Registered_Hours]),SUM('apticproject view_workedtime'[Worked.1]))

View solution in original post

hi @amitchandak, it's a one to many relationship

 

darko861_0-1618995818525.png

the yellow highlighted at the top is worked time table, the bottom left is the employee table and the bottom right is the user table which connects the tables based on userid (worked time) and memberid (employee table)

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.