Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
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?
Solved! Go to 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])
Thank you all guys, your advices helped me a lot!
This worked:
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!
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
)
)
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])
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
117 | |
104 | |
77 | |
73 | |
52 |
User | Count |
---|---|
145 | |
109 | |
108 | |
90 | |
64 |