Forum Discussion
Relationship between 2 tables
Hi savanahubio,
Based on my test, you should be able to use the formula below to create a new measure to calculate the forecast value, then show the created measure as Line Values on the chart to get the expected result without creating any relationships in your scenario.
Measure = CALCULATE ( SUM ( 'POS Forecast'[POS Forecast] ), FILTER ( 'POS Forecast', CONTAINS ( 'Daily Sales by Item', 'Daily Sales by Item'[Item Description], 'POS Forecast'[Item Description] ) && 'POS Forecast'[Year] = MAX ( 'Daily Sales by Item'[Year] ) && 'POS Forecast'[Week Num] = MAX ( 'Daily Sales by Item'[Week Num] ) ) )Here is the modified pbix file for your reference. :smileyhappy:
Regards
I did the opposite of this an created the Week Ending in my Forecast table because that is what I go by. Because I have item detail in the Forecast table there are 3 values for any given week. I cannot create a relationship because there arent unique values. I think I need to create a table with just the WeekNum and the Last Day of the week and then make a relationship between both other tables.
I do not know how to go that but I will try!
Microsoft EmployeeHi savanahubio,
Could you share a sample pbix file(with just some sample/mock data) which can reproduce the issue, so that we can further assist on it? You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading. :smileyhappy:
Regards
Hello,
Sorry about the late reply! Here is the link https://drive.google.com/open?id=1Ivhtgj5F9Xw5J0nqIZkMbvWz3yeu4mr6
I want do do this and then be able to filter by Item Description
- v-ljerr-msft
Hi savanahubio,
There is only one table in your shared pbix file. Could you also shared the other table, so that I can try to find a way to create the relationship for you? :smileyhappy:
Regards