DAX Calculated Column

[Anonymous]

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? 

[Anonymous]

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])

[rainer1]

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]

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

[amitchandak · Accepted solution]

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])