Forum Discussion

daromel's avatar
daromel
Frequent Visitor
5 years ago
Solved

How to avoid sum duplicates values in a measure

Hi! Please I need help. I have a table like this:     I need a measure that sum all the values (Column VALUE) except the row that duplicates ID and PRODUCT. I want to put this measure the ...
  • Anonymous's avatar
    Anonymous
    5 years ago

    Why don't you de-duplicate the table before you load it into PBI? In Power Query it's just as easy to do as clicking several buttons... And if you want to know how many dup rows there ware originally for a given row, you can store the count of the row in the table as well. This will retain all the information you have in the original table and the resultant table will be as small as possible, thus DAX will be faster on it.

  • Anonymous's avatar
    Anonymous
    5 years ago
    [Measure] =
SUMX(
    SUMMARIZE(
        T,
        T[ID],
        T[Product],
        T[Value]
    ),
    T[Value]
)
Anonymous's avatar
Anonymous
Not applicable
5 years ago

Why don't you de-duplicate the table before you load it into PBI? In Power Query it's just as easy to do as clicking several buttons... And if you want to know how many dup rows there ware originally for a given row, you can store the count of the row in the table as well. This will retain all the information you have in the original table and the resultant table will be as small as possible, thus DAX will be faster on it.

daromel's avatar
daromel
Frequent Visitor
5 years ago

Thanks for the answering, 

The table has others fields that I need. 

No all the fields are duplicates, just some of then.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago
    [Measure] =
SUMX(
    SUMMARIZE(
        T,
        T[ID],
        T[Product],
        T[Value]
    ),
    T[Value]
)
    • daromel's avatar
      daromel
      Frequent Visitor
      5 years ago

      It worked perfect. thank you!!