Forum Discussion
There's not enough memory to complete this operation. when creating a measure
In brief, I have a list of duplicated IDs in [Code] column, for each code value multiple [Avail Time] values, at [Last Avail] column: I need to keep the maximum [Avail Time] value for one record then return the other value to be 0
Seq
Seq = COUNTAX(FILTER(CMS,[Avail Time]<=EARLIER([Avail Time])&&[Code]=EARLIER([Code])),[Code])
Max
Max Avail = maxx(Filter(CMS,[Code]=EARLIER([Code])),[Seq])
Last Avail
Last Avail = if([Seq]=[Max],[Avail Time],BLANK())
as mentioned the below example
thanks in advance
AAbdelkader says file is no longer available?
vanessafvg I uploaded the updated one
AAbdelkader ok i have now.
still need to understand the business requirement here, are you trying to find the sequence of events by the code? what are you trying to do with the data, the reason i am asking is because the power bi model doesn't like the calculated columns, i would have created the combo columns (code) in power query for starters, but i can't access that part of the model because i dont have access to the source so i can't test that. Also why are you combining code with date? is that to keep it unique? Adding an index column is probably a better way to do that (you can also do that in power query)
if you could just give more of an explanation around what you want to do with the data that would make this easier.
ok i just changed the data types of your fields because using text was making it ugly and values and concatenate = performance hungry.
i am still not sure if this is what you want it to do but this is what ive done and it ran succesfully (took a minute or 2 though), still thinking creating a code in power query might be better.
date2 = FORMAT(CMS[Date],"General Number")
Code2 = CONCATENATE(CMS[date2],CMS[Login ID])
Seq = COUNTAX(FILTER(CMS,[Avail Time]<=EARLIER([Avail Time])&&[Code2]=EARLIER([Code2])),[Code2])