Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Delete duplicates after columns are calculated in DAX

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 calcu...
Need Help
Anonymous's avatar
Anonymous
Not applicable
4 years ago

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's avatar
ryan_mayu
Icon for Super User rankSuper User
4 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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's avatar
      ryan_mayu
      Icon for Super User rankSuper User
      4 years ago

      Anonymous 

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

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        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