Forum Discussion

P_Stavis's avatar
P_Stavis
Regular Visitor
1 year ago
Solved

DAX - Remove duplicates and sum the values

Hello!   I'm trying to use DAX to remove the duplicates, based on a column and then sum the values of another column. Manually removing the duplicates in PQ returns the correct values however my D...
  • P_Stavis's avatar
    P_Stavis
    1 year ago

    Heres the formula that did the job:

     

    For price:

     

    Sum_Price_Ok =
    SUMX(
        DISTINCT('Sample Data'[Product_ID]),
        CALCULATE(
            FIRSTNONBLANK('Sample Data'[Price], 0)
        )
    )
     
    For the weight:
     
    Sum_Weight_Ok =
    SUMX(
        DISTINCT('Sample Data'[Product_ID]),
        CALCULATE(
            FIRSTNONBLANK('Sample Data'[Item_weight], 0)
        )
    )
xifeng_L's avatar
xifeng_L
Icon for Super User rankSuper User
1 year ago

Hi P_Stavis 

 

You could try adding a calculated column to identify the row numbers in the interval:

 

Then just add the filter condition of row number equal to 1 in the measure:

 

 

 

Did I answer your question? If yes, pls mark my post as a solution and appreciate your Kudos !

 

Thank you~

  • P_Stavis's avatar
    P_Stavis
    Regular Visitor
    1 year ago

    Hi xifeng_L 

    I did download your sample pbix and also tried to reproduce but unfortunately I could only reach this point:

     

    I did get your idea of having 1 for the first line and 2 for the repeated line. For the sample data it would work however for a larger database i don't think it would be the best solution.
    Do you see another way to solve this problem only using dax measures?

    Thanks alot for your reply!