Forum Discussion
Helper IICalculation if ID match
Hey all,
i need to create a calculation that substract an amount from table1 from table2. (Amount from table1 - amount from table2). This should be based on if the ID's match. So if Reference ID from table1 match the ID from table2, then do the substraction, else nothing.
i have posted a screenshot of an example.
8 Replies
ryan_mayuSuper User
create relationship between two tables and create two columns
Column = RELATED('Table 1'[reference id])+0 Column 2 = if(ISBLANK(sumx(FILTER('Table 1','Table 2'[id]='Table 1'[reference id]),'Table 1'[amount])),0,'Table 2'[amount]-sumx(FILTER('Table 1','Table 2'[id]='Table 1'[reference id]),'Table 1'[amount]))pls see the attachment below
Robin96Hey, Thanks for the response! There is no relation between these two ID's in the original semantic model. They both relate to the date table i have. Is there a way to add this link with for example TREATAS in the column syntax?
- Ashish_Mathur
Hi,
In the last row of the desired output table, shouldn't the answer be 300?
- Robin96
Hey, yes. The last row of Desired output should be 300. thanks for highlighting this.
- Ashish_Mathur
Hi,
In table2, write these calculated column formulas
Table1 reference ID = calculate(max(Table1[reference ID]),filter(Table1,Table1[reference ID]=earlier(Table2[id])))
Amount = calculate(sum(Table1[amount]),filter(Table1,Table1[reference id]=earlier(Table1 reference ID)))
Hope this helps.
