Forum Discussion
Sum of column values from another table using left join
Hi,
I am trying to get sum of column values from another table using a left join. I have three tables TableA, TableB and TableC
I would like to get sum of values from TableC and would like to get value of 0 when there are no matching record for TableA in TableC.
Below is the query I have and i am getting records only for matching items (guessing inner join)
CALCULATETABLE(SUMMARIZECOLUMNS('TableA'[id], FILTER(ALLNOBLANKROW('TableB'), 'TableB'[EndDate] >= NOW() "ServiceHours",SUM('TableC'[Hours])))
Summarize by TableA->Id, Filter for EndDate in future from TableB and then get hours from TableC.
Result is only including Ids which has values in TableC
Could you please help with this query?
Hi leo006 ,
Not very clear the relationship between TableB and other two tables.
Regardless TableB, you could refer below formula.
tmp = ADDCOLUMNS(TableA,"ServiceHours",IF(TableA[ID] in VALUES(TableC[ID]),CALCULATE(SUM(TableC[Hours]),FILTER(TableC,TableC[ID]=TableA[ID])),0))TableA:
TableC:
Result:
If I misunderstood your meaning, please show some sample data and expected result.
Best Regards,
Jay
2 Replies
- amitchandak
Super User
leo006 , You need use generate or crossjoin with filter
example
filter(generate(TableA, TableB), TableA[ID] = Table2[ID1] && TableA[EndDate] > TableB[Date])
You can not have same column name in cross join
For all DAX joins refer
https://www.sqlbi.com/articles/from-sql-to-dax-joining-tables/
Hi leo006 ,
Not very clear the relationship between TableB and other two tables.
Regardless TableB, you could refer below formula.
tmp = ADDCOLUMNS(TableA,"ServiceHours",IF(TableA[ID] in VALUES(TableC[ID]),CALCULATE(SUM(TableC[Hours]),FILTER(TableC,TableC[ID]=TableA[ID])),0))TableA:
TableC:
Result:
If I misunderstood your meaning, please show some sample data and expected result.
Best Regards,
Jay
