PowerBi Dax: sum only first row in duplicates

Question

I have the following table

Location	Domain	Phase	Answer	Domain score
loc1	A	1	1	1
loc1	A	2	1	0.5
loc1	A	2	0	0.5
loc1	A	3	0	0
loc1	A	4	0	0
loc2	A	1	1	1
loc2	A	2	1	1
loc2	A	2	1	1
loc2	A	3	0	0
loc2	A	4	0	0

Now I want to sum the domain score per Location and domain but for duplicate phases I only want to sum the first row. so for phase 2 i only want to add a single Domain score. Which would result in:

Location	Domain	Phase	Answer	Domain score	End score
loc1	A	1	1	1	1.5
loc1	A	2	1	0.5	1.5
loc1	A	2	0	0.5	1.5
loc1	A	3	0	0	1.5
loc1	A	4	0	0	1.5
loc2	A	1	1	1	2
loc2	A	2	1	1	2
loc2	A	2	1	1	2
loc2	A	3	0	0	2
loc2	A	4	0	0	2

Is this possible in DAX? I allready tried to generate a kind of rownumber partion by() construction but I cannot get it working...

Answer 1

I will use "Data" as a name for your table. Create a measure (not a calculated column):

End Score =
CALCULATE (
    SUMX (
        SUMMARIZE (
            Data,
            Data[Location],
            Data[Domain],
            Data[Phase],
            Data[Domain score]
        ),
        Data[Domain score]
    ),
    ALLEXCEPT ( Data, Data[Domain], Data[Location] )
)

Result:

How it works:

• SUMMARIZE function takes all your columns except "Answer", and creates a virtual table where every row is unique, thus suppressing duplicates;
• SUMX iterates over this new table, and sums up domain scores;
• ALLEXCEPT then makes sure that results are not filtered by any fields except location and domain.

Note though, the better way to solve this problem is to answer why your data has these duplicates in the first place, and if they could be removed from the data model. Using fancy DAX to fix data issues is not a good practice.