Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
AAbdelkader
Helper I
Helper I

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

9 REPLIES 9
vanessafvg
Super User
Super User

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





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




@vanessafvg

 

Capture.PNG

 

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

 

Capture.PNG

 

Download Max If.pbix file

 

thanks in advance

@AAbdelkader says file is no longer available?





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




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





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




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

 

 

 

 





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




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

 

Capture.PNG

 

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,

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

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

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





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.