cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

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

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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

Re: 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 () )

4.png

 

Best Regards

Maggie

View solution in original post

4 REPLIES 4
Highlighted
Resolver II
Resolver II

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

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.

 

1.png

 

Regards,

CR

 

Highlighted
Super User I
Super User I

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

@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])

 

image.png

 

Note - In your expected output, for ClientId 6 the ProductCodeId should be 01A01 but not 01A02 isn't it ?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Frequent Visitor

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

@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

dateifmin.PNG

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é

 

 

Highlighted
Community Support
Community Support

Re: 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 () )

4.png

 

Best Regards

Maggie

View solution in original post

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors