Forum Discussion

AAbdelkader's avatar
AAbdelkader
Icon for Helper I rankHelper I
9 years ago

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

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 ...
Show and Tell
AAbdelkader's avatar
AAbdelkader
Icon for Helper I rankHelper I
9 years ago

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's avatar
ImkeF
Icon for Community Champion rankCommunity Champion
9 years ago

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