March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
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
Solved! Go to Solution.
in dax:
new table=filter(table1,VAR _max=calculate(max(table1[costs]),allexcept(table1,table1[username],table1[typ])) RETURN table1[costs]=_max)
in dax:
new table=filter(table1,VAR _max=calculate(max(table1[costs]),allexcept(table1,table1[username],table1[typ])) RETURN table1[costs]=_max)
It works, thank you very much wdx223_Daniel
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
27 | |
17 | |
16 | |
12 | |
11 |
User | Count |
---|---|
37 | |
29 | |
26 | |
20 | |
16 |