Forum Discussion

ybatistamayo's avatar
ybatistamayo
Helper III
6 years ago
Solved

RELATIONSHIP

I have two tables, one of "Cost" and one of "Taxes", in the latter the "tax" column is updated every year. also the column that I have to relate both tables is "IDCOST". The first problem is that I...
Show and Tell
  • Ashish_Mathur's avatar
    Ashish_Mathur
    6 years ago

    That file has an extension of .7z.  I cannot open that on my system.

  • v-lili6-msft's avatar
    v-lili6-msft
    6 years ago

    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

v-lili6-msft's avatar
v-lili6-msft
Community Support
6 years ago

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

  • ybatistamayo's avatar
    ybatistamayo
    Helper III
    6 years ago
     

     Excellent!, it worked perfect. Could you explain to me what exactly this calculation is doing?