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

Ashish_Mathur's avatar
Ashish_Mathur
Super User
6 years ago

Hi,

Share the link from where i can download your PBI file.

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    6 years ago

    Hi,

    There is no file there.  To avoid confusion, please only share 2 Tables - Cost and Tax.  Also, share the correct result for the data that you share.

    • ybatistamayo's avatar
      ybatistamayo
      Helper III
      6 years ago

      well here it goes again only with two original tables

      https://we.tl/t-zyQ1y6R9GM

       

       

      and the result I aspire to get is this

       

      total = quantity * price * tax

       

       

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Super User
        6 years ago

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