Forum Discussion
Create filtered table based on max value
Hi, have been using Power BI for a while and I'm trying to get better in DAX
I have a problem that I'm trying to solve for months ( have a working solution in power query but the query update is very slow process )
So I am trying to create a calculated table where it returns the row that have the max value, with context filters by the columns "Data", "Emp" ( this is relative) and "Numero"
So from this data sample I would want it to return the rows with red and orange text ( this will be applied to a bigger dataset with more months)
| Data | Numero | Emp | TOTAL | Supervisor |
| 01/12/2020 | 400790 | P30 | 40 | |
| 01/12/2020 | 400790 | P30 | 50 | Ana Antunes |
| 01/12/2020 | 400790 | P44 | 35 | Filipe Almeida |
| 01/12/2020 | 400790 | P44 | 40 | Filipe Leite |
| 01/12/2020 | 400790 | P30 | 10 | Filipe Almeida |
I've tried many approaches but none seems the "right" way of dealing with it
Would apprecciate all of your inputs on how you would deal with this problem
Thank you guys
I am a bit short of time to test this.
Can you use this and test it please?
TableX = VAR _tab = ADDCOLUMNS(TableY, "MaxTot", CALCULATE(MAX(TableY[TOTAL]), ALLEXCEPT(TableY, TableY[Emp], TableY[Data], TableY[Numero]))) RETURN FILTER(_tab, TableY[TOTAL] = [MaxTot])
5 Replies
I am a bit short of time to test this.
Can you use this and test it please?
TableX = VAR _tab = ADDCOLUMNS(TableY, "MaxTot", CALCULATE(MAX(TableY[TOTAL]), ALLEXCEPT(TableY, TableY[Emp], TableY[Data], TableY[Numero]))) RETURN FILTER(_tab, TableY[TOTAL] = [MaxTot])"returns the row that have the max value" Is this from the Total column?
Why is the orange line getting returned (
01/12/2020 400790 P44 40 Filipe Leite) )
when the Total in the previous line is higher?
I'm still not seeing it. The P44 rows have Total of 41 and 40 and the 40 row is in orange .
Silly of me, painted the wrong one when I pasted the data the second time, changed the value to make it right 😅
