Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ybatistamayo
Helper III
Helper III

add calculated columns with date filters

Yesterday I had an intelligent answer by v-lili6-msftthis time I wanted something similar but to have the values ​​of the "tax" table in the "Cost" table according to the corresponding year.

step to explain

I have two tables, in fact "Cost" is a transactional table of the cost of sale. The second "Tax" table contains two columns of% and amount of taxes. The problem is that they are updated every year and used to calculate total cost, which is cost + tax.I want to add to each record of my "Cost" table the value of the "Tax" table according to the corresponding year.The problem is that the many-to-many relationship is not the most appropriate for this case.The common field of both tables is "IDCOST".Thank you!

here the pbxi>https://we.tl/t-pJnEeT1yXi 

 

1.png

 

2.png

 

3.png

 

 

8 REPLIES 8
YJ
Resolver II
Resolver II

Hi, @ybatistamayo 

There you go a solution for your result table , ontop of that , all your measures should work:

Yak01.JPG

There is no way that you can use the IDCOST directly and not have a many to many because it is not unique... I created a NEWID  in the background to maintain the relationship

Attached for you : PBIX 

& result table as per ask for

 

Regards

 

 

 

Actually, what I was waiting for was columns calculated in the "Cost" table. 

no measures, The result has an error in the sum of both calculated fields. thank you anyway

 

hi ,

what error did you see, did you download and run it from the PBIX file i sent?

regards

yes, I saw the file you sent me

1- It's great how you resolved the issue of the relationship between the tables. But what happens if the "tax" table does not have dates for 2017 and the "Cost" fact table does? I get 0 in the calculation of "TAX2" and "TAX1Amount", it was what happened to me when I took it to the real stage.

2- for some reason the sum of this last calculation is "151" when in fact it must be "247"

3-I really appreciate your help

Here is the file for those who requested that they could not open

https://we.tl/t-Ltwbf9fHx4

ybatistamayo
Helper III
Helper III

Here the actual link https://we.tl/t-RE8vNWk49v

Hi,

can you provide the source file:

 

graciasYak.JPG

here the link of data https://we.tl/t-SZZzLrWbRK

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.