Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello everyone,
I need some help! I have two tables. The first one has all of the order description and client information and the other shows all the information regarding the delivery of each order. It is possible that each order has 2 or more Tracking Numbers with different delivery dates.
What I'm trying to do is to get the last delivery date from table 2 for each order of the table 1.
I have tried many times to no avail, so right I'm using the "Firstnoblank" formula, but this only returns the first value it finds instead of the last one by date.
Table 2:
Delivery Information | ||
Order | Tracking Number | Delivery Date |
123 | 10001 | 1/1/2020 |
124 | 10002 | 2/1/2020 |
125 | 10003 | 3/1/2020 |
126 | 10004 | 4/1/2020 |
125 | 10005 | 5/1/2020 |
123 | 10006 | 6/1/2020 |
Table 1:
Orders | |
Order | Last Delivery Date |
123 | 6/1/2020 |
124 | 2/1/2020 |
125 | 5/1/2020 |
126 | 4/1/2020 |
Thank you all!
Solved! Go to Solution.
Hi @Grevatious ,
Would you please refer to the following measure:
last delivery date =
CALCULATE (
MAX ( 'Delivery Information'[Delivery Date] ),
ALL ( 'Delivery Information' ),
'Delivery Information'[Order] IN DISTINCT ( Orders[Order] )
)
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Dedmon Dai
Hey - I want to use something like this for a calculated column. If I use this measure, it does not work correctly, how would I work around it?
Hi @Grevatious ,
Would you please refer to the following measure:
last delivery date =
CALCULATE (
MAX ( 'Delivery Information'[Delivery Date] ),
ALL ( 'Delivery Information' ),
'Delivery Information'[Order] IN DISTINCT ( Orders[Order] )
)
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Dedmon Dai
Use this measure in you table to display the date
Measure =
VAR __id = MAX( 'Table'[Orders] )
VAR __date = CALCULATE ( MAX( 'Table'[Delivery date] ), ALLSELECTED ( 'Table' ), 'Table'[Orders] = __id )
RETURN CALCULATE ( MAx ( 'Table'[Delivery date] ), VALUES ( 'Table'[Orders] ), 'Table'[Orders] = __id, 'Table'[Delivery date] = __date )
Probably in Table1,
Last delivery date = MAXX(FILTER(RELATEDTABLE('Table2'),'Table2'[Order]=[Order]),[Delivery date])
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
105 | |
104 | |
86 | |
73 | |
66 |
User | Count |
---|---|
124 | |
113 | |
98 | |
81 | |
72 |