topic Re: Delete rows and groupby in DAX Commands and Tips

Delete rows and groupby

Learner86 — Mon, 23 Aug 2021 06:56:07 GMT

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

Re: Delete rows and groupby

wdx223_Daniel — Mon, 23 Aug 2021 08:40:43 GMT

in dax:

new table=filter(table1,VAR _max=calculate(max(table1[costs]),allexcept(table1,table1[username],table1[typ])) RETURN table1[costs]=_max)

Re: Delete rows and groupby

Learner86 — Mon, 30 Aug 2021 12:22:52 GMT

It works, thank you very much wdx223_Daniel