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:
Amedeo,
I apologize for my lack of knowledge here, and please do forgive me the stupid question:
is this a single Measure or should I make to separate for Max Depth and Result?
If I make 2 measures I get the error "A function MAX has been used in a True/False expression that is used as a table filter expression. This is not allowed".
If I combine them in 1 measure I get this error: "the syntax for 'VAR' is incorrect (...)
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:
Amedeo you were right! I was missing the name of the measure!
I compared the result with the one I produced earlier and all matches but I will use yours as you suggested as for sure is more correct.
Again many thanks, not only for the solution but also because I learned a lot!
Kudos for you Amedeo 🙂 Have a great weekend