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.
Hi,
I need to find a solution to find the most recent date for all the lines who have the same ID.
Here is an exemple:
In the exemple below, I'm searching for a formula that will search in the column ID and Version all the lines with the same ID and the same version, then return "OK" for the one with the most recent date.
ID1 | Version | Date | Solution I want |
123 | v1 | 22/03/2020 | KO |
123 | v1 | 23/09/2020 | OK |
123 | v1 | 20/05/2020 | KO |
G0 | v1 | 12/12/2020 | OK |
G0 | v2 | 01/01/2020 | KO |
AV | v1 | 19/11/2020 | KO |
AV | v1 | 20/11/2020 | OK |
G0 | v1 | 05/05/2020 | KO |
123 | v1 | 02/02/2020 | KO |
123 | v2 | 16/04/2020 | KO |
123 | v2 | 17/04/2020 | OK |
G0 | v2 | 24/09/2020 | OK |
I hope that it is clear.
Thank you for your help !
Solved! Go to Solution.
Hi, @Anonymous
Please try the below.
newest =
VAR newest =
CALCULATE ( MAX ( Data[Date] ), ALLEXCEPT ( Data, Data[ID1], Data[Version] ) )
RETURN
IF ( MAX ( Data[Date] ) = newest, "OK", "KO" )
Hi, My name is Jihwan Kim.
If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.
Linkedin: https://www.linkedin.com/in/jihwankim1975/
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Hi, @Anonymous
Please try the below.
newest =
VAR newest =
CALCULATE ( MAX ( Data[Date] ), ALLEXCEPT ( Data, Data[ID1], Data[Version] ) )
RETURN
IF ( MAX ( Data[Date] ) = newest, "OK", "KO" )
Hi, My name is Jihwan Kim.
If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.
Linkedin: https://www.linkedin.com/in/jihwankim1975/
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Thank you Jihwan. It didn't work at the beginning but when I deleted the max() in your last line, it worked ! Thank you for your help !
@Anonymous you could try this measure:
Solution =
VAR MaxDate =
CALCULATE ( MAX ( 'Table'[Date] ), REMOVEFILTERS ( 'Table'[Date] ) )
RETURN
IF ( SELECTEDVALUE ( 'Table'[Date] ) = MaxDate, "OK", "KO" )
Respectfully,
Zoe Douglas (DataZoe)
Follow me on LinkedIn at https://www.linkedin.com/in/zoedouglas-data
See my reports and blog at https://www.datazoepowerbi.com/
Hi DataZoe,
Thank you for you help but it didn't work. It doesn't filter the colums ID and versions to find the most recent dates with the same ID/versions
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 |
---|---|
114 | |
98 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |