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
Hi,
Power BI is Excel like but you shoudn't think like that way.
Below I will describe my solution, but first I suggest you read this article about data modeling in Power BI Desktop written by radacad : https://radacad.com/basics-of-modeling-in-power-bi-fact-tables
1/ Refer to that article, you are actually having 2 fact tables : cost and tax.
which share common dimension tables : Dates, Products and Costs
2/ To create:
-Date table : https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/
-Products table : In Edit Query, do a duplicate of the Cost table and Keep only column Product, do a DISTINCT on that column
-Costs table : In Edit Query, do a duplicate of the Cost table and Keep only column IDCOST, do a DISTINCT on that column
3/ Your model will have 5 tables :
-new table issues from step 2 : Dates, Products, Costs
-your origin table : Fact_Sales, Fact_Tax
-By folowwing the link I sent at the beginning : https://radacad.com/basics-of-modeling-in-power-bi-fact-tables you will be able to link these tables by using they key (all of them shoudl be 1 to many relationship)
-at the end, you can create your measure Total = quanity * price + tax
Best,
when you put cgo tax to values in the matrix this is what happens
what I want is every year to show the correct tax