RELATIONSHIP

[ybatistamayo]

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.

[Ashish_Mathur · Accepted solution]

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

[Yakob_Logerfo]

I need help, new to Power bi, How can I create relationship between this table?

[v-lili6-msft · Accepted solution]

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]

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

[Ashish_Mathur]

Hi,

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

[ybatistamayo]

The file has three other tables that I suggested that a previous user add to the model

Here the link

https://we.tl/t-OKPRfpoVgx 

[Ashish_Mathur]

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]

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 · Accepted solution]

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

[ybatistamayo]

https://we.tl/t-YuVCU8vQfP

[Ashish_Mathur]

Hi,

I need to carry out a transformation in the Query editor.  Share the source Excel files which have both tables.

[YJ]

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

[leed]

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,

[ybatistamayo]

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

[ybatistamayo]

I'm sorry I'm sorry, I'm wrong to type, the right thing is that each product is assigned an IDCosto

[ybatistamayo]

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