Forum Discussion
dax remove duplicates except one
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:
But honestly I have no clue, because I don't need to sum up the cumulative costs
kind regards
colin
Hi Anonymous ,
Hi Anonymous ,
Like this?
Since you already have the cumulative value, you only need to take the maximum value under each category.
4 Replies
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 , 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]))
)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.
