topic Re: Sum values until the first maximum value in another column per each category in another column in DAX Commands and Tips

Sum values until the first maximum value in another column per each category in another column

Ecan20 — Thu, 25 Feb 2021 10:01:29 GMT

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

Re: Sum values until the first maximum value in another column per each category in another column

Anonymous — Thu, 25 Feb 2021 12:40:16 GMT

This task does not have a solution since you have not stated how values are ordered. Without a column that tells you the order of values, the description is meaningless.

Re: Sum values until the first maximum value in another column per each category in another column

Ecan20 — Thu, 25 Feb 2021 12:49:19 GMT

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!

Re: Sum values until the first maximum value in another column per each category in another column

AmedeoM — Thu, 25 Feb 2021 22:29:53 GMT

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 🙂

Re: Sum values until the first maximum value in another column per each category in another column

daxer-almighty — Fri, 26 Feb 2021 00:30:04 GMT

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.

Re: Sum values until the first maximum value in another column per each category in another column

Ecan20 — Fri, 26 Feb 2021 11:10:34 GMT

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.

Regards

Elena

Re: Sum values until the first maximum value in another column per each category in another column

Anonymous — Fri, 26 Feb 2021 11:30:12 GMT

Hello 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

Re: Sum values until the first maximum value in another column per each category in another column

Ecan20 — Fri, 26 Feb 2021 11:40:08 GMT

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?

Re: Sum values until the first maximum value in another column per each category in another column

Anonymous — Fri, 26 Feb 2021 11:46:09 GMT

Yes, sorry, I didn't write the code explicitly because I assumed you had stored the value in a variable anyway. Please try with the following code:

Measure := VAR _MaxDepth = MAX ( Table[Depth] ) VAR _Result = CALCULATE ( MIN ( Table[DAY] ), KEEPFILTERS ( Table[DEPTH] = _MaxDepth ) ) RETURN _Result

And let me know if it works 🙂

Re: Sum values until the first maximum value in another column per each category in another column

Ecan20 — Fri, 26 Feb 2021 12:02:46 GMT

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 (...)

Re: Sum values until the first maximum value in another column per each category in another column

Anonymous — Fri, 26 Feb 2021 12:11:08 GMT

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:

Re: Sum values until the first maximum value in another column per each category in another column

Ecan20 — Fri, 26 Feb 2021 13:19:50 GMT

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