Dax Measure with if statement and columns from different tables

[darko861]

Hi community,

I have the following visual:

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:

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:

the table v_rpt_apticcustom_Employee_Time_Billingv6 looks like this:

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?

[darko861 · Accepted solution]

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.

looks much better now:

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?

[amitchandak]

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

is it Many -Many ?

[darko861 · Accepted solution]

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.

looks much better now:

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?

[darko861 · Accepted solution]

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]))

[darko861]

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

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)