If condition with max date

[atifbutt]

Hi,

I'm building dashboard in Direct Query mode.

Data = Asatdate, UserID, Name, Ethnicity

I'd like to create a oclumn "Ethnicity Type" and to put values from Ethnicity column if the UserID field is not duplicate, if it is duplicate then put the latest value for Ethnicity by date.

Thank you in advance for the help.

Thanks

Atif

[v-shex-msft]

Hi @atifbutt,

Please share some sample data for test and coding formula.

Regards,

Xiaoxin Sheng

[atifbutt · Accepted solution]

Thanks for replying Xiaoxin. I've found the solution via sql query :).

[Stuznet]

Could you kindly post the SQL solution?

Thanks

[atifbutt]

Select field1,field2,field3
from (select field1,field2, field3,
row_number() over (partition by employeenumber order by (datefield "to pick the latest record") desc) as rn from tablename) as T
Where RN = 1

[Stuznet]

Thank you so much

[atifbutt]

Select field1,field2,field3
from (select field1,field2, field3,
row_number() over (partition by employeenumber order by (datefield "to pick the latest record") desc) as rn from tablename) as T
Where RN = 1