Forum Discussion
Sum values until the first maximum value in another column per each category in another column
Amedeo,
I modified it a bit and I got what I needed. Instead of the single days (second column) I used the cumulative days as input.
This formula returned the amount of days required to reach the final depth
VAR_RESULT =CALCULATE (MIN (Table[DAY]),FILTER(Table, Table[DEPTH] = (MAX(Table[DEPTH]))))Many thanks for taking the time to help, much appreciated.RegardsElena
No need to apologize Ecan20 🙂
Yes, that would be a single measure, in which you can define multiple variables and reference them in the same measure. I assumed you were using a fairly recent version of Power BI Desktop/Excel, in which you can define variables.
Are you using a recent version of Power BI Desktop/Excel/SQL Server Analysis Services?
I don't want to ask a stupid question either, but have you forgot to provide a name for your measure? That's the first thing I can think of.
If you miss that, you'll get an error:
With measure name instead:
Hello,
this will return 9 for all the rows in your table (I don't know if that's the output you wanted - if it's not, please provide a demo file and a detailed explanation of what you would like the measure to compute and I'll try to fix it):
Measure :=
VAR _MaxDepth =
CALCULATE (
MAX ( Table[Depth] ),
REMOVEFILTERS ( Table[Depth] )
)
VAR _Result =
CALCULATE (
SUM ( Table[Day] ),
Table[Depth] < _MaxDepth
)
RETURN
_Result
Please let me know if this works 🙂
Amedeo,
I modified it a bit and I got what I needed. Instead of the single days (second column) I used the cumulative days as input.
This formula returned the amount of days required to reach the final depth
VAR_RESULT =CALCULATE (MIN (Table[DAY]),FILTER(Table, Table[DEPTH] = (MAX(Table[DEPTH]))))Many thanks for taking the time to help, much appreciated.RegardsElenaHello Ecan20 ,
happy to know that it helped 🙂
Just be aware that usually passing a whole table as a filter to CALCULATE is not a good idea because it could negatively affect performance (and sometimes also return unpredictable results as well). Perhaps it would be better if you used:KEEPFILTERS ( Table[DEPTH] = MAX ( Table[DEPTH] ) )as a filter, insead.
Best regards,
Amedeo
Amedeo,
thanks for this, I tried it but I am getting this error "A function MAX has been used in a True/False expression that is used as a table filter expression. This is not allowed"... any idea what I may be doing incorrectly?