Forum Discussion
Distinct column/table where value in column DateIn is oldest date
Hi CornerACK
Create a calculated column in your original table
final = VAR mindate_client = CALCULATE ( MIN ( 'original table'[DateIn] ), ALLEXCEPT ( 'original table', 'original table'[ClientId] ) ) RETURN IF ( 'original table'[DateIn] = mindate_client, mindate_client, BLANK () )Best Regards
Maggie
CornerACK If you want to achieve this in DAX then please try below as New Table
Test160Out = SUMMARIZE(Test160Grouping,Test160Grouping[ClientId],Test160Grouping[GenderId],Test160Grouping[AgeId],Test160Grouping[BirthDate],Test160Grouping[CityId],"DateIn",MIN(Test160Grouping[DateIn]))
Then add a New Column for ProductCodeId to get the productCodeId for the earlierst DateIn
ProductCodeId = LOOKUPVALUE(Test160Grouping[ProductCodeId],Test160Grouping[DateIn],Test160Out[DateIn],Test160Grouping[ClientId],Test160Out[ClientId])
Note - In your expected output, for ClientId 6 the ProductCodeId should be 01A01 but not 01A02 isn't it ?
Thanks so far for your help!
I came across another issue. Maybe you can also help me with that?
I also want a filter (likewise the answers you gave) in my table
I created an If statement where ClientId exists more then 1 time in my column ClientId.
If so I want the Column "Meting" to be filled with the MIN date (in above example 20160815). If it is the other date I want it to be empty in de column "Meting".
This way I don't need to create a new table. Is this possible and if so, how can I accomplish this?
Kind regards,
Corné
Hi CornerACK
Create a calculated column in your original table
final = VAR mindate_client = CALCULATE ( MIN ( 'original table'[DateIn] ), ALLEXCEPT ( 'original table', 'original table'[ClientId] ) ) RETURN IF ( 'original table'[DateIn] = mindate_client, mindate_client, BLANK () )Best Regards
Maggie