get sum of distinct values from joined table in DAX

[siva_powerbi]

Hello Experts

I have two tables that have a common field ID,

Table1 has 2 records

ID     Emp     Amount    Month

1       abc        2000        01-July-2021

2       abc        1000         02-July-2021

Table 2 has 9 records

ID    Emp       Time (Seconds)     Month

1      abc        10000                     01-July-2021

1      abc        10000                     02-July-2021

1      abc        10000                     03-July-2021

1      abc        10000                     04-July-2021

1      abc        10000                     05-July-2021

1      abc        10000                     06-July-2021

1      abc        10000                     07-July-2021

1      abc        10000                     08-July-2021

1      abc        10000                     09-July-2021

When I join (Inner join) on both tables result set is 18 records, means data is getting duplocated.

Here when I take sum of amount I should get 3000 but I am getting 27000 (Everything is multiplied by 9) same is case with duration.

Unable to develop a DAX formula to get 3000 for amount, Looking for help from experts.

[amitchandak]

@siva_powerbi , Try a measure like 

Sumx(summarize(Table1,Table1[Emp], Table[Amount]), [Amount])

[siva_powerbi]

I have tried this approach but I am getting sum of amount for table not for individual employees, I need to get sum for individual employees. Any idea on how to do this?

[v-kelly-msft · Accepted solution]

Hi  @siva_powerbi ,

First create a Union table as below:

union table = 
var _tabl=SELECTCOLUMNS('Table1',"ID",'Table1'[ID],"Emp",'Table1'[Emp],"Month",'Table1'[Month],"Amount",'Table1'[Amount])
var _tab2=SELECTCOLUMNS('Table2',"ID",'Table2'[ID],"Emp",'Table2'[Emp],"Month",'Table2'[Month])
var _tab3=ADDCOLUMNS(_tab2,"Amount",CALCULATE(MAX('Table1'[Amount]),FILTER('Table1','Table1'[ID]=EARLIER([ID])&&'Table1'[Emp]=EARLIER([Emp])&&'Table1'[Month]=EARLIER([Month]))),"TimeSecond",CALCULATE(MAX('Table2'[Time (Seconds)     ]),FILTER('Table2','Table2'[ID]=EARLIER([ID])&&'Table2'[Emp]=EARLIER([Emp])&&'Table2'[Month]=EARLIER([Month]))))
var _tab4=ADDCOLUMNS(_tabl,"TimeSecond",CALCULATE(CALCULATE(MAX('Table2'[Time (Seconds)     ]),FILTER('Table2','Table2'[ID]=[ID]&&'Table2'[Emp]=[Emp]&&'Table2'[Month]=[Month]))))
Return
DISTINCT(UNION(_tab3,_tab4))

The Union table is shown as below:

Then you could get the sum of individual employees simply by a table visual,such as below:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[siva_powerbi]

Thanks for the approach, though didn't follow the same route but used the idea and got the result.

[siva_powerbi]

Thanks for the answer, will try and let you know,

One more query do I need to write this measure in Table1 or resultant table after joining table1 and table2?