Create filtered table based on max value

[tdrp]

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

[HotChilli · Accepted solution]

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])

[HotChilli]

I'm still not seeing it.  The P44 rows have Total of 41 and 40 and the 40 row is in orange .

[tdrp]

Silly of me, painted the wrong one when I pasted the data the second time, changed the value to make it right 😅

[tdrp]

Hi @HotChilli , thank you for your response

Yes, the max value is from the total, forgot to mention that

The other row is returned beacause the column "Emp" has a different ID, it's another condition to filter by

Thank you 

[HotChilli]

"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?