Forum Discussion
Sum values until the first maximum value in another column per each category in another column
Hello DAX experts,
I have a dataset like the table below.
I am looking for a DAX formula to return the sum of the values in DAY column until the values in DEPTH column reach the first maximum value, per each category listed in JOB column.
The desired result for job A for example would be 9 days = sum of days to reach the maximum DEPTH (500 in this case).
The job A takes 13 days in total, but I only want the days until the maximum depth is reached.
Thanks to anyone that will try to help!
| JOB | DAY | DEPTH |
| A | 1 | 100 |
| A | 1 | 150 |
| A | 1 | 200 |
| A | 1 | 300 |
| A | 1 | 300 |
| A | 1 | 300 |
| A | 1 | 400 |
| A | 1 | 400 |
| A | 1 | 500 |
| A | 1 | 500 |
| A | 1 | 500 |
| A | 1 | 500 |
| A | 1 | 500 |
| B | 1 | 50 |
| B | 1 | 50 |
| B | 1 | 100 |
| B | 1 | 200 |
| B | 1 | 300 |
| B | 1 | 350 |
| B | 1 | 400 |
| B | 1 | 550 |
| B | 1 | 550 |
| B | 1 | 550 |
| B | 1 | 550 |
| B | 1 | 550 |
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.RegardsElenaNo 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:
11 Replies
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
Hi daxer, thanks for the hint. But forgive me, I am a beginner and I don't understand what you mean by "the order of the values". Which values? What would you suggest me doing? Thanks!
- daxer-almighty
Solution Sage
DAX does not have an intrinsic notion of order. All tables in the model are unordered, just like tables in SQL. So, in order to calculate something that resembles "running totals," you have to have a column that orders rows in the table. Otherwise, the result of aggregation will be indetermined/random.
