Delete duplicates after columns are calculated in DAX

[jij19]

I have a table with IDs in a column which have Sub IDs in another column and each row can have a different service entered date. In the same table there are a lot of other columns and also some calculated columns which also should be displayed in the output, but these columns don't change. They just also belong to the rows.

Example:

ID     Sub ID            Entered date

10	10001		02/05/2022
10	10002		02/10/2022
10	10003		02/12/2022
20	20001		03/02/2022
20	20002		02/20/2022

I want to get this output by deleting duplicated id's and only take the first entered row:

ID      Sub ID           Entered date

10	10001		02/05/2022
20	20002		02/20/2022

[PaulDBrown]

Here is one way of creating a measure to filter the rows in the table:

Filter Measure =
COUNTROWS (
    SUMMARIZE (
        FILTER (
            'Table',
            'Table'[EnteredDate]
                = CALCULATE ( MIN ( 'Table'[EnteredDate] ), ALLEXCEPT ( 'Table', 'Table'[ID] ) )
        ),
        'Table'[ID],
        'Table'[Sub ID],
        'Table'[EnteredDate]
    )
)

Add the measure to the filters for the visual in the filter pane and set the value to 1:

[jij19]

Doesn't work for me with this code. I still get duplicates with that

[ryan_mayu]

@jij19 

pls try this

sub id = maxx(FILTER('Table','Table'[ID]=max('Table'[ID])&&'Table'[Date]=min('Table'[Date])),'Table'[SubID])

enterdate = min('Table'[Date])

pls see the attachment below

[jij19]

Yes the output is good but I would like to have it as a new table output and npt in the dashboard view, sorry didn't mentioned that

So it would be some DAX code.

Do you know how to do that?

[ryan_mayu]

you can try this

Table 2 = 
var tbl=SUMMARIZE('Table','Table'[ID],"date2",min('Table'[Date]))
return ADDCOLUMNS(tbl,"SUBID",maxx(FILTER('Table','Table'[ID]=EARLIER('Table'[ID])&&'Table'[Date]=[date2]),'Table'[SubID]))

[jij19]

this one is good, but is there any way how I can get all the other columns from the old dataset to this new table. the rest of the columns should also be displayed. A way to extend the code and add the rest of the columns without any rule?

[ryan_mayu]

@jij19 

You can try this

Table 2 = 
VAR TBL=ADDCOLUMNS('Table',"CHECK",IF('Table'[ENTERDATE]=CALCULATE(MIN('Table'[ENTERDATE]),ALLEXCEPT('Table','Table'[ID])),1))
return FILTER(TBL,[CHECK]=1)

or

Table 2 = 
VAR TBL=ADDCOLUMNS('Table',"CHECK",IF('Table'[ENTERDATE]=CALCULATE(MIN('Table'[ENTERDATE]),ALLEXCEPT('Table','Table'[ID])),1))
VAR TBL2=FILTER(TBL,[CHECK]=1)
RETURN SELECTCOLUMNS(TBL2,"ID",'Table'[ID],"SUBID",'Table'[SUBID],"ENTERDATE",'Table'[ENTERDATE])

[jij19]

The first one with check would be good but I still get duplicated rows. So the duplicates are still in the output. 

In the second code it doesn't show any added "check" column in the output.

Thanks in advance

[jij19]

Example:

ID     Sub ID            Entered date          Team

10	10001		02/05/2022		A
10	10002		02/10/2022		A
10	10003		02/12/2022		B
20	20001		03/02/2022		C
20	20002		02/20/2022		C

I want to get this output by deleting duplicated id's and only take the first entered row:

ID      Sub ID           Entered date         Team

10	10001		02/05/2022		A
20	20002		02/20/2022		C

Maybe this helps. So the table contains a lot more columns which also should be displayed in the output. 

[ryan_mayu]

@jij19 

pls try this

Table 2 = 
VAR TBL=ADDCOLUMNS('Table',"CHECK",IF('Table'[ENTERed DATE]=CALCULATE(MIN('Table'[ENTERed DATE]),ALLEXCEPT('Table','Table'[ID])),1))
VAR TBL2=FILTER(TBL,[CHECK]=1)
RETURN SELECTCOLUMNS(TBL2,"ID",'Table'[ID],"SUBID",'Table'[SUBID],"ENTERDATE",'Table'[ENTERed DATE],"team",'Table'[Team])

if you have more columns, just add them in the selectcolumn function.

[jij19]

That looks almost good! unfortunately I still get duplicates with this code

[ryan_mayu]

@jij19 

maybe you need to provide more sample data. 

or  try this

Table 2 = 
VAR TBL=ADDCOLUMNS('Table',"CHECK",IF('Table'[ENTERed DATE]=CALCULATE(MIN('Table'[ENTERed DATE]),ALLEXCEPT('Table','Table'[ID])),1))
VAR TBL2=FILTER(TBL,[CHECK]=1)
VAR TBL3= SELECTCOLUMNS(TBL2,"ID",'Table'[ID],"SUBID",'Table'[SUB ID],"ENTERDATE",'Table'[ENTERed DATE],"team",'Table'[Team])
RETURN SUMMARIZE(TBL3,[ID],[SUBID],[ENTERDATE],[team])

[jij19]

I think it is the right solution. I still get duplicates but this is because I have IDs with multiple rows (Sub ID) with the same entered date. So it takes all rows with the min entered date if there are more than one with the same one. 

Is there a way tho include the Sub ID as well. I mean that the code looks for the min entered date and also the min Sub ID number?

Thank you @ryan_mayu 

[ryan_mayu]

@jij19 

In order to provide the better solution, could you pls provide the sample data?