Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

DAX Calculated Column

Hi, this is my request:

 

I need to find [Date] of the previous row by date (highest date less than the current date) adding these conditions: 

  • [First_Parameter] of this row must be equal to [First_Parameter] of the row found.
  • [Second_Parameter] of this row must be different from [Second_Parameter] of the row found. 

 

I created this calculated column: 

VAR X =

CALCULATE(MAX(My_Table[Data]),

FILTER(My_Table, My_Table[Data] < EARLIER(My_Table[Data])),

FILTER(My_Table, My_Table[First_Parameter] = EARLIER(My_Table[First_Parameter])),

FILTER(My_Table, My_Table[Second_Parameter] <> EARLIER(My_Table[Second_Parameter])))

 

Error: 

"There's not enough memory to complete this operation, please try again later when there may be more memory available."

 

Do you have any idea how I could rewrite this DAX expression? 

 

 

Need Help
  • amitchandak's avatar
    amitchandak
    6 years ago

    Try a new column like, you can add condition using && (and) || or , inside the same filter

     

    Last Date Time = maxx(filter(table,table[Date Time]<earlier(table[Date Time]) && table[param1] =earlier(table[param1]) && table[A] =earlier(table[A])),table[Date Time])
    Max value = maxx(filter(table,table[team] =earlier(table[team]) && table[A] =earlier(table[A])),table[B])

4 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    Try a new column like, you can add condition using && (and) || or , inside the same filter

     

    Last Date Time = maxx(filter(table,table[Date Time]<earlier(table[Date Time]) && table[param1] =earlier(table[param1]) && table[A] =earlier(table[A])),table[Date Time])
    Max value = maxx(filter(table,table[team] =earlier(table[team]) && table[A] =earlier(table[A])),table[B])

  • rainer1's avatar
    rainer1
    Resolver III
    6 years ago

    Hi Anonymous,

     

    the earlier function is really slow and memory intensive. There are a few possible solutions:

     

    At first build up a Index column based on the date by this Dax

    Index = 
VAR CurrentObjectID = 'My_Table'[object_id]
RETURN
RANKX (
FILTER (
'My_Table'; 
'My_Table'[object_id] = CurrentObjectKey
); 
'My_Table'[DATE]; ; ASC; Dense
)

      if you don't have a object_id you can use this 

    Index = 
RANKX (
'My_Table'; 
'My_Table'[DATE]; ; ASC; Dense
)

    or build an index in Power Query Editor if you don't want to sort it by date.

    May be you already have a index column then leave this step out.

     

    Then build up the variable by this:

     

    new_column =
var prev_number = 'My_Table'[index] -1
varx = LOOKUPVALUE('My_Table'[Date]; 'My_Table'[First_Parameter];'My_Table'[First_Parameter];'My_Table'[index];prev_number)

     this will Lookup for the date of the previous column by the index.

     

    -------------------------------------------------------------------
    Did I answer your question? Mark my post as a solution!
    It was useful? Press Thumbs Up!

     

  • Mariusz's avatar
    Mariusz
    Community Champion
    6 years ago

    Hi Anonymous 

     

    Try this

    Measure = 
VAR __date = SELECTEDVALUE( My_Table[Data] )
VAR __firstParameter = SELECTEDVALUE( My_Table[First_Parameter] )
VAR __secondParameter = SELECTEDVALUE( My_Table[Second_Parameter] )
RETURN 
CALCULATE(
    MAX( My_Table[Data] ),
    FILTER(
        ALL( My_Table[Data], My_Table[First_Parameter], My_Table[Second_Parameter] ),
        My_Table[Data] < __date &&
        My_Table[First_Parameter] = __firstParameter &&
        My_Table[Second_Parameter] <> __secondParameter
    )
)

     

    Best Regards,
    Mariusz

    If this post helps, then please consider Accepting it as the solution.

    Please feel free to connect with me.
    LinkedIn

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Thank you all guys, your advices helped me a lot!

     

    This worked:

    MAXX(FILTER(Table, Table[Data Inserimento] < EARLIER(Table[Data]) && Table[First] = EARLIER(Table[First]) && Table[Second] <> EARLIER(Table[Second])), Table[Data])