Forum Discussion
Problem with Many to Many relationships to get max date record based on a slicer
Hi, I am new to Power BI development.I need help to resolve the problem below.
I have three tables(Table1,Table2 and CalenderTable). Table1 has Many to Many relationships with Table2. I have added a date slicer based on CalenderTable. If the user selects one date on the date slicer, I need to find max date(Max Date should be less than Slicer Date) records in Table2 for each Product ID and need to display all the Table1 columns along with the Table2 Price Column by using ProductID.
Table1:
Table2:
Expected Final Result 😞Let's assume the user Picked date in Slicer is 28-Mar-2022 )
Model View:
Rajakkr1996 OK, I think I got this. See PBIX attached below signature.
Measure = VAR __CalendarDate = MAX('CalendarTable'[Date]) VAR __Product = MAX('Table1'[ProductID]) VAR __MaxDate = MAXX(FILTER('Table2', [ValueDate] <= __CalendarDate), [ValueDate]) VAR __Value = MAXX(FILTER('Table2',[ValueDate] = __MaxDate),[Price]) VAR __Result = IF(HASONEVALUE(CalendarTable[Date]) && __MaxDate <= __CalendarDate, __Value, BLANK()) RETURN __Result
4 Replies
- Greg_Deckler
Community Champion
Rajakkr1996 Can you paste that sample data as text in a table so I can mock it up?
Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882
Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.
Greg_Deckler Thanks for the reply and guidance. Sample data for your reference,
Table1:RowID ProductID OrderedDate 1 1 18-03-2022 2 1 19-03-2022 3 2 20-03-2022 4 2 21-03-2022 5 2 22-03-2022 6 3 23-03-2022 7 3 24-03-2022 8 3 25-03-2022 9 4 26-03-2022 10 4 27-03-2022 Table2:
ProductID ValueDate Price 1 01-02-2022 100.00 1 02-02-2022 200.00 2 22-02-2022 155.67 2 02-02-2022 143.78 2 17-02-2022 356.89 3 22-02-2022 557.43 3 07-02-2022 111.32 3 08-02-2022 888.87 4 19-02-2022 567.89 4 10-02-2022 222.44 Expected Output:
RowID ProductID OrderedDate Price 1 1 18-03-2022 200.00 2 1 19-03-2022 200.00 3 2 20-03-2022 356.89 4 2 21-03-2022 356.89 5 2 22-03-2022 356.89 6 3 23-03-2022 111.32 7 3 24-03-2022 111.32 8 3 25-03-2022 111.32 9 4 26-03-2022 567.89 10 4 27-03-2022 567.89 - Greg_Deckler
Rajakkr1996 OK, I think I got this. See PBIX attached below signature.
Measure = VAR __CalendarDate = MAX('CalendarTable'[Date]) VAR __Product = MAX('Table1'[ProductID]) VAR __MaxDate = MAXX(FILTER('Table2', [ValueDate] <= __CalendarDate), [ValueDate]) VAR __Value = MAXX(FILTER('Table2',[ValueDate] = __MaxDate),[Price]) VAR __Result = IF(HASONEVALUE(CalendarTable[Date]) && __MaxDate <= __CalendarDate, __Value, BLANK()) RETURN __Result
