cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
CL7777 Frequent Visitor
Frequent Visitor

trying to extract a value from a many to many relationship

Hi all,

I am an intermediate dax user but Im struggling to figure out how to do this.

 

I have a table (call it Sales) with part number and sale transaction date. I have a second table (call it Pricing) with a list of part numbers and MSRP selling prices with start dates and end dates. So, in this second table, I have part numbers listed several times with date ranges (start dates and end dates) for each new price that they have had over time. This creates a many to many relationship if I try to connect part number in the two tables.

 

What I want to do is create a calculated column in the Sales table that looks at the Pricing table and picks the MSRP for the date range which the transaction date is in. In other words, for a given line in the Sales table, take the part number and the transaction date, and look in the Pricing table for the part number and the start date and end date range that contains the transaction date and report back to the column the MSRP that from the Pricing table that meets that criterion. 

 

here is what I came up with, but it doesnt work

 

Column =
VAR
pricehistorytable = SUMMARIZE('_Part Price History', [Part Number], [Start Date], [End Date], [Unit Price])
VAR
abbreviatedTable =
ADDCOLUMNS(CALCULATETABLE(pricehistorytable, FILTER(pricehistorytable, '_Revenue All Component ss'[Part Number] = [Part Number])), "correctMSRPflag", IF([Transaction Date] > [Start Date] && [Transaction Date] < [End Date], "T", "F"))
RETURN
CALCULATE(MINX(abbreviatedTable, [Unit Price]), FILTER(abbreviatedTable, [correctMSRPflag] = "T"))
 
Am I on the right track? It doesnt work. Thanks for your help,
 
CL7777
1 ACCEPTED SOLUTION

Accepted Solutions
elofstrom Regular Visitor
Regular Visitor

Re: trying to extract a value from a many to many relationship

Here's what worked for me:

I have a Product Pricing table with ProductKey, StartDate, EndDate, and Price.

I have a Sales table that has ProductKey and Date.

In the Sales table I created the following column:

Price at Date =
VAR _ProductKey = Sales[ProductKey]
VAR _SaleDate = Sales[Date]
VAR _Return = CALCULATE(MIN('Product Pricing'[Price]), FILTER(ALL('Product Pricing'), 'Product Pricing'[ProductKey] = _ProductKey && 'Product Pricing'[StartDate] <= _SaleDate && 'Product Pricing'[EndDate] >= _SaleDate))
RETURN _Return
 
Seems to do the trick.
 
Hope that helps.
Eric
 

View solution in original post

2 REPLIES 2
elofstrom Regular Visitor
Regular Visitor

Re: trying to extract a value from a many to many relationship

Here's what worked for me:

I have a Product Pricing table with ProductKey, StartDate, EndDate, and Price.

I have a Sales table that has ProductKey and Date.

In the Sales table I created the following column:

Price at Date =
VAR _ProductKey = Sales[ProductKey]
VAR _SaleDate = Sales[Date]
VAR _Return = CALCULATE(MIN('Product Pricing'[Price]), FILTER(ALL('Product Pricing'), 'Product Pricing'[ProductKey] = _ProductKey && 'Product Pricing'[StartDate] <= _SaleDate && 'Product Pricing'[EndDate] >= _SaleDate))
RETURN _Return
 
Seems to do the trick.
 
Hope that helps.
Eric
 

View solution in original post

CL7777 Frequent Visitor
Frequent Visitor

Re: trying to extract a value from a many to many relationship

thats perfect... thank you!!

Helpful resources

Announcements
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 136 members 1,597 guests
Please welcome our newest community members: