Forum Discussion

Khushboo9966's avatar
Khushboo9966
Helper II
2 years ago

How do I remove duplicates based on unique values from two columns  in DAX.

How do I remove duplicates based on Name and _totals for _standardize new? 

For example: 

Name = Adam

_totals =  5

Standardize new = 19.29%.
How can 19.29% show only once for unique name and _totals?

File: SampleAnalysis/TestFile.pbix at main · khushs9966/SampleAnalysis · GitHub

 

What I have tried: I tried creating an dense rank index of name and _ totals then use summarize. It did not give me intended result. 

 

What I am trying to accomplish: I need to take an average of _Standardize new, because there are duplicates the average is significantly lower and wrong. 

Need Help

4 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    2 years ago

    Select these two columns, then "Remove Duplicates".  You will end up with distinct values on these two columns, and random values on all other columns.

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi Khushboo9966 ,

     

    I see your requirements. You can try my workaround.

    Since you have dates, you can rank based on it.

    rank = RANKX(FILTER('Table',[Name]=EARLIER('Table'[Name])&&[_Totals]=EARLIER('Table'[_Totals])),[Decision Date],,ASC,Dense)

    Then create a calculated table to remove duplicates.

    Table 2 = FILTER('Table',[rank]=1)

     

    Best Regards,

    Stephen Tao

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.