There's not enough memory to complete this operation. when creating a measure

[AAbdelkader]

Hello,

please I need help as I tried to apply a DAX measure to get MAXIF result, but I get this error 

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

in applying this function 

Seq = COUNTAX(FILTER(CMS,[Avail2]<=EARLIER([Avail2])&&[Code]=EARLIER([Code])),[Code])

while my data set isn't huge anymore only 36k rows. I ask what the potential reason for such error

thanks in advance

[vanessafvg]

@AAbdelkader maybe you should tell us what you trying to do, there could be a variety of reasons why

also what data types are you fields

and the countax function, what are you trying to do there count values or could a countrows work instead?

[AAbdelkader]

@vanessafvg, 

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

Download Max If.pbix file

thanks in advance

[vanessafvg]

@AAbdelkader says file is no longer available?

[AAbdelkader]

@vanessafvg I uploaded the updated one

[vanessafvg]

@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.

[vanessafvg]

@AAbdelkader

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

[AAbdelkader]

@vanessafvg

sorry it still loading more then give me the mentioned error, I need to clarify what the result that I need to get, regardless the used equation, you could try an easier one. 

we will override the query that I applied, I've 2 columns [Code] has duplicated records, [Avail Time] has multiple records for such code, I need [Max Avail] column, I need to get the maximum avail record for every [Code] value then return the other to be 0 like listed in pic

thanks a lot,

[ImkeF]

If you use the query-editor instead, you will not experience these performance problems here, because you can "partition" your tabel on the code and operate on much smaller chunks there:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Login ID", Int64.Type}, {"Avail Time", Int64.Type}, {"Code", Int64.Type}}),
    GroupCode = Table.Group(#"Changed Type", {"Code"}, {{"Partition", each _, type table}}),
    Function = (Table) =>  
let
    Seq = Table.AddColumn(Table, "Seq", each Table.RowCount(Table.SelectRows(Table, (this)=>this[Avail Time]<=[Avail Time]))),
    #"Added Custom1" = Table.AddColumn(Seq, "Max Avail", each List.Max(Seq[Seq])),
    #"Added Custom" = Table.AddColumn(#"Added Custom1", "Last Avail", each if [Seq]=[Max Avail] then [Avail Time] else null)
in #"Added Custom",
#"Added Custom" = Table.AddColumn(GroupCode, "Custom", each Function([Partition])),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Date", "Login ID", "Avail Time", "Seq", "Max Avail", "Last Avail"}, {"Date", "Login ID", "Avail Time", "Seq", "Max Avail", "Last Avail"})
in
    #"Expanded Custom"

or if you just need the "Last Avail":

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Login ID", Int64.Type}, {"Avail Time", Int64.Type}, {"Code", Int64.Type}}),
    GroupCode = Table.Group(#"Changed Type", {"Code"}, {{"MaxAvail", each List.Max([Avail Time]), type number}, {"OtherColumns", each _, type table}}),
    #"Expanded OtherColumns" = Table.ExpandTableColumn(GroupCode, "OtherColumns", {"Date", "Login ID", "Avail Time"}, {"Date", "Login ID", "Avail Time"}),
    #"Added Custom1" = Table.AddColumn(#"Expanded OtherColumns", "Max Avail", each if [MaxAvail]=[Avail Time] then [MaxAvail] else null)
in
    #"Added Custom1"

How to integrate M-code into your solution  -- Check out more PBI- learning resources here

[vanessafvg]

@AAbdelkader so this might be a dumb question why dont you group by thee relevant fields ie code date etc and then get the max time.

so in other words in power query, create the composite key that makes the code unique by creating a custom column and then group by that key and bring max the max available time, or am i misunderstanding your requirement?