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

[Ecan20]

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

[Ecan20 · Accepted solution]

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

[AmedeoM]

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 🙂

[Ecan20 · Accepted solution]

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

[Anonymous]

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

[Ecan20]

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? 

[Anonymous]

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 🙂

[Ecan20]

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

[Anonymous · Accepted solution]

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:

[Ecan20]

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

[Anonymous]

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.

[Ecan20]

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]

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.