Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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])
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])
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 😅
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
"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?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
108 | |
98 | |
78 | |
66 | |
53 |
User | Count |
---|---|
139 | |
100 | |
95 | |
85 | |
63 |