dax remove duplicates except one

[Anonymous]

Hello Everyone 

Help me please!! I cannot look at it anymore.

I have a Table like this:

Table Errorcosts

Week Number of the year	Errorplace	Cumulative Errorcosts
1	Sales	100
1	Production	300
2	Sales	330
2	Production	400
2	Production	400
3	Production	500
3	Sales	530
4	Production	600
4	Production	600
4	Production	600
4	Sales	560
5	Production	700
5	Production	700
5	Sales	670
6	Production	800
6	Sales	700
7	Production	900
7	Sales	800
7	Sales	800
7	Sales	800
8	Production	10000
8	Sales	900
8	Sales	900

Now I want to show in a line chart the cumulative costs till the present week. Thats why I made a connection through week number of the year to a date dimension table (DimDates). I then want to be able to filter this chart in Sales or Production with a simple slicer but also see the cumulative total cost. 

My problem is I cannot change the Table because of other important data i didnt show in the example.

 So I tried to creat a measure:

Measure = CALCULATE(SUM('Errorcosts'[Cumulative Errorcosts]),Filter(ALLSELECTED(DimDates[Week number of the year]),DimDates[Week number of the year]<=MAX(DimDates[Week number of the year])))

But honestly I have no clue, because I don't need to sum up the cumulative costs 

kind regards 

colin

[Anonymous]

proud of th final result!

Thanks for the help

[v-xuding-msft · Accepted solution]

Hi @Anonymous ,

Hi @Anonymous ,

Like this?

Since you already have the cumulative value, you only need to take the maximum value under each category.

[Anonymous]

Thank you amitchandak

but its not working the way I want to. Because of the sum in formula the graph in the linie chart is far to big i think.

[amitchandak]

@Anonymous , based on what I got

Better create Errorplace as a new dimension and create like

Measure = CALCULATE(SUM('Errorcosts'[Cumulative Errorcosts])
,Filter(ALLSELECTED(DimDates[Week number of the year]),DimDates[Week number of the year]<=MAX(DimDates[Week number of the year]))
, filter( allselected(Errorplace) , Errorplace[Errorplace] =max(Errorplace[Errorplace]))
)