Forum Discussion

Ecan20's avatar
Ecan20
Frequent Visitor
5 years ago
Solved

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 val...
dax formula
  • Ecan20's avatar
    Ecan20
    5 years ago

    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's avatar
    Anonymous
    5 years ago

    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's avatar
Ecan20
Frequent Visitor
5 years ago

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's avatar
Anonymous
Not applicable
5 years ago

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's avatar
    Ecan20
    Frequent Visitor
    5 years ago

    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's avatar
      Anonymous
      Not applicable
      5 years ago

      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's avatar
        Ecan20
        Frequent Visitor
        5 years ago

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