Distinct column/table where value in column DateIn is oldest date

[CornerACK]

Hello,

I have a question regarding to filtering unique values in a table. In the sample below I have some cliënts with products attachted. In the column DateIn we see the date when they where registered.

ClientId	GenderId	AgeId	BirthDate	CityId	ProductCodeId	DateIn
1	Female	71	19470324	1676	01A01	20170626
1	Female	71	19470324	1676	01A01	20180101
2	Male	64	19540916	716	01A02	20160815
3	Male	64	19541119	664	02A01	20180122
4	Male	80	19380304	1676	01A01	20160815
4	Male	80	19380304	1676	01A02	20170401
5	Male	83	19350124	716	07A01	20161003
6	Male	75	19430103	664	01A02	20160815
6	Male	75	19430103	664	01A01	20141027
7	Male	89	19290606	654	01A01	20160815
8	Male	92	19260222	703	01A01	20160815

What I want is this table: (only to show the rows with oldest DateIn)

ClientId	GenderId	AgeId	BirthDate	CityId	ProductCodeId	DateIn
1	Female	71	19470324	1676	01A01	20170626
2	Male	64	19540916	716	01A02	20160815
3	Male	64	19541119	664	02A01	20180122
4	Male	80	19380304	1676	01A01	20160815
5	Male	83	19350124	716	07A01	20161003
6	Male	75	19430103	664	01A02	20141027
7	Male	89	19290606	654	01A01	20160815
8	Male	92	19260222	703	01A01	20160815

In this case 20170626 is older date then 20180101, so I don't want 20180101 to show in my table with ClientId 1.

The same I want for ClientId 4 and 6.

Do I need to make a duplicate table or something? Or can I fix this with a DAX formula?

Thanks in advance for taking your time for my issue!

Kind regards,

Corne

[PattemManohar]

@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 ?

[CornerACK]

@PattemManohar@CR

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é

[v-juanli-msft · Accepted solution]

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

[CR]

Hi @CornerACK

In your Query editor, you can use that code (you just need to customize the source or just to start from the lines you need, such as Removed Columns).

let
    Source = Excel.Workbook(File.Contents(" your source "), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"ClientId", Int64.Type}, {"GenderId", type text}, {"AgeId", Int64.Type}, {"BirthDate", Int64.Type}, {"CityId", Int64.Type}, {"ProductCodeId", type text}, {"DateIn", Int64.Type}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"GenderId", "AgeId", "CityId", "ProductCodeId"}),
    #"ID - adding" = Table.AddColumn(#"Removed Columns", "ID", each Text.Combine({Text.From([ClientId], "fr-FR"), Text.From([DateIn], "fr-FR")}, "_"), type text),
    #"ID - sort" = Table.Sort(#"ID - adding",{{"ID", Order.Ascending}}),
    #"Index 1 - adding" = Table.AddIndexColumn(#"ID - sort", "Index", 1, 1),
    #"Index 2 - adding" = Table.AddIndexColumn(#"Index 1 - adding", "Index.1", 2, 1),
    #"Merged Queries - index 1 & Index 2" = Table.NestedJoin(#"Index 2 - adding",{"Index.1"},#"Index 2 - adding",{"Index"},"Index 2 - adding",JoinKind.LeftOuter),
    #"Merged Queries - expande ClientID" = Table.ExpandTableColumn(#"Merged Queries - index 1 & Index 2", "Index 2 - adding", {"ClientId"}, {"Expanded ClientID"}),
    #"OLDEST DATEIN - adding" = Table.AddColumn(#"Merged Queries - expande ClientID", "OLDEST DATEIN", each if [ClientId] = [Expanded ClientID] then "NO" else "YES")
in
    #"OLDEST DATEIN - adding"

It is a little bit crasy but it works fine. Indeed, after having sort on the new ID column (concatenation of ClientID ad DateIn), I added 2 indexes to make a Merge Queries inside the same table (as PowerBi is working by line, not by cell, I cheat with double indexes to display the ClientID value from the next line). Then, I just need to add a calculated column saying that => IF ClientID from next line is the same as the current ClientID then display NO else YES.

Regards,

CR