Forum Discussion
RELATIONSHIP
That file has an extension of .7z. I cannot open that on my system.
hi, ybatistamayo
For your case, you don't need to create the relationship between two tables, just try this formula to create a measure:
Result = var _table=ADDCOLUMNS(COST,"_Tax",CALCULATE(SUM(TAX[TAX]),FILTER(TAX,COST[IDCOST]=TAX[IDCOST]&&YEAR(COST[DATE])=YEAR(TAX[DATE])))) return SUMX(_table,[QUANTITY]*[PRICE]+[_Tax])
Result:
and here is sample pbix file, please try it.
Regards,
Lin
Very interesting qns for something seemingly straight forward.
If i get what you are intending to do, can i say for each IDCOST, the ID is not important , its only the year:
100 is 2 in 2018 regardless of A or B
100 is 4 in 2019
101 is 3 in 2018.
If that is the case, in table 1 , let newIDCOST= Year&IDCOST ( example 2018_100) , do this in source or power querry
in table 2 concatenate this to newIDCOST,Tax
2018_100, 2
2019_100,4
2018_101,3
(remove duplcates is necessary(after removing column ID, row 1,2 would be same)
create a 1 to many relationship from table 2 to table 1.
to be neat you can have a seperate calendar table or if your table is not too complicated just have a year column in your table 2.
regards