cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Penn Regular Visitor
Regular Visitor

Creating relationship between two tables

Hi Everyone,

 

I have two tables, sales table and price table as below.

 

Sales Table

Sales IDItem IDInvoice Date
SO0001IT000230/04/2018
SO0002IT000230/05/2019

 

Price Table

Item IDFrom DateEnd DatePrice
IT000201/01/190015/04/2018$100.00
IT000216/04/201815/05/2018$101.00
IT000216/05/201801/01/1900$102.00

 

The relationship on Item ID will be many to many between these two tables. Noted that the dafult setting for null date is 01/01/1900.

 

Is there any way that I can do in Power BI to get the correct price ($102.00) for Sales ID SO0002? Thanks

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Creating relationship between two tables

@Penn try following calculated columns, In expression, change table and column name as per your data model.

 

Price = 
VAR itemid = Table4[Item ID]
VAR invdate = Table4[Invoice Date]
RETURN 
CALCULATE( 
    MAX( Table3[Price] ), 
    Table3[Item ID] = itemid,
    invdate >= Table3[From Date] ,
    invdate <= IF( YEAR( Table3[End Date] ) = 1900, invdate, Table3[End Date]) )




Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Kudos Help Smiley Happy
Connect with me on Linkedin. Feel free to email me with any of your BI needs.




 




3 REPLIES 3
Super User
Super User

Re: Creating relationship between two tables

@Penn try following calculated columns, In expression, change table and column name as per your data model.

 

Price = 
VAR itemid = Table4[Item ID]
VAR invdate = Table4[Invoice Date]
RETURN 
CALCULATE( 
    MAX( Table3[Price] ), 
    Table3[Item ID] = itemid,
    invdate >= Table3[From Date] ,
    invdate <= IF( YEAR( Table3[End Date] ) = 1900, invdate, Table3[End Date]) )




Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Kudos Help Smiley Happy
Connect with me on Linkedin. Feel free to email me with any of your BI needs.




 




Penn Regular Visitor
Regular Visitor

Re: Creating relationship between two tables

Hi @parry2k 

 

You are absolutely a legend! Never thought that I can do this in just one step. Learned something new today. Thanks

Highlighted
Super User
Super User

Re: Creating relationship between two tables

@Penn glad to help.





Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Kudos Help Smiley Happy
Connect with me on Linkedin. Feel free to email me with any of your BI needs.