Forum Discussion
Helper IRemove duplicates without changing values in calculated column
I managed to solve this by uploading again the same data set under a different name. I've removed the duplicates in the second data set and used FIRSTNONBLANK function to add the calculated Tot column of the first data set to the second data set. A bit of an unconventional approach I would say, but it does the trick!
This idea was inspired by az38. Thank you!
Community Championif you created new columns with DAX expressions - no, dataset won't be removed correct in any case
Helper II don't get your answer. Perhaps my problem was not very clear. Allow me to elaborate.
Consider the following example with just one ID:
where Tot is calculated as follows: Tot = calculate(sum(val), allexcept(ID)).
I want to remove the duplicate IDs and just keep one ID without the Tot column to change. Now, if I remove the duplicate ID rows with edit query, I get that Tot=10, but I want the final result to look like this:
- az38
what's rule for duplicates definition? why do you remove exactly 2019, 2018 and 2017 rows and not 2020?
- bamba98
az38 Because I want to keep the latest period an ID was active and after removing the duplicates I don't care much about the column containing Val.
- az38
try to create new calculated table like
Table 2 = ADDCOLUMNS( SUMMARIZE('Table', 'Table'[ID], "Period", MAX('Table'[Period])), "Val", CALCULATE(SUM('Table'[Val]), FILTER('Table', 'Table'[ID] = EARLIER([ID]) && 'Table'[Period] = EARLIER([Period]))), "Tot", CALCULATE(SUM('Table'[Tot]), FILTER('Table', 'Table'[ID] = EARLIER([ID]) && 'Table'[Period] = EARLIER([Period]))) )