Forum Discussion
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 tried the "Many to many" relationship and it didn't work.
I want to relate both tables to get a final result as shown.
that is, calculate the tax according to the year of the date column.
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
15 Replies
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,
I'm sorry I'm sorry, I'm wrong to type, the right thing is that each product is assigned an IDCosto
A 100
B 103
C 101
D 102
I'm sorry I'm sorry, I'm wrong to type, the right thing is that each product is assigned an IDCosto
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
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
Hi,
Share the link from where i can download your PBI file.
The file has three other tables that I suggested that a previous user add to the model
Here the link
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.
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
Excellent!, it worked perfect. Could you explain to me what exactly this calculation is doing?
I need help, new to Power bi, How can I create relationship between this table?
