Forum Discussion
Learner86
Helper I
Helper IDelete rows and groupby
Dear community,
how can I solve the following problem:
| Username | Typ | Costs | Frequency | Index |
| Musterman | M | 125 | 6 | 1 |
| Musterman | M | 3200 | 6 | 2 |
| Musterman | M | 3200 | 6 | 3 |
| Musterman | M | 400 | 6 | 4 |
| Saunders | N | 3800 | 1 | 5 |
| Wooten | K | 3200 | 1 | 6 |
| Brown | B | 3800 | 2 | 7 |
| Brown | B | 125 | 2 | 8 |
I want to keep all rows with frequency 1 and if the frequency is bigger than 1, then I want to keep the most expensive one (regarding the column costs). That means I want to keep the rows with the index number 2, 3, 5, 6, 7 and delete the rows with the numer 1 , 4, 8. How can I do this?
Thank you very much!
Best regards
in dax:
new table=filter(table1,VAR _max=calculate(max(table1[costs]),allexcept(table1,table1[username],table1[typ])) RETURN table1[costs]=_max)
2 Replies
- wdx223_Daniel
Community Champion
in dax:
new table=filter(table1,VAR _max=calculate(max(table1[costs]),allexcept(table1,table1[username],table1[typ])) RETURN table1[costs]=_max)
Learner86It works, thank you very much wdx223_Daniel