Forum Discussion
Getting not enough memory error on powerbi
When i run the below dax formula i'm getting the not enough memory error. How do I fix it please
Impact =
VAR Success = SELECTCOLUMNS(Filter(Payment,Payment[TransactionStatus]="true"),"Cust_id_Month",CONCATENATE([Cust_Id],Payment[Month]))
Return IF(CONCATENATE(Payment[Cust_Id],Payment[Month]) in Success,"No","Yes")
I'm trying to see if the payment failure had impact on churn. I.e. did the customer have a successful payment that month.
I'm filtering on successful transaction, storing it as a table "Success" and then checking if the cust_Id, Month combination for each payment is found in Success. If found then it's found then "No" the payment failure had no impact on churn for that month, if not found then "yes" the payment failure had impact on churn.
Hi SGV ,
Here's an optimized version of your DAX formula:
Column:
Impact = VAR Success = CALCULATETABLE ( VALUES ( Payment[Cust_Id] ), Payment[TransactionStatus] = "true", ALLEXCEPT ( Payment, Payment[Month] ) ) RETURN IF ( CONTAINS ( Success, Payment[Cust_Id], Payment[Cust_Id] ), "No", "Yes" )Measure:
Impact Measure := VAR Success = CALCULATETABLE ( VALUES ( Payment[Cust_Id] ), Payment[TransactionStatus] = "true", ALLEXCEPT ( Payment, Payment[Month] ) ) VAR CurrentCustIdMonth = CONCATENATE ( SELECTEDVALUE ( Payment[Cust_Id] ), SELECTEDVALUE ( Payment[Month] ) ) RETURN IF ( CONTAINS ( Success, Payment[Cust_Id], SELECTEDVALUE ( Payment[Cust_Id] ) ), "No", "Yes" )This formula uses and to create a table of unique values with successful transactions for each month. Then, it uses the function to check if the is in the table. If it is, the formula returns "No", otherwise, it returns "Yes".
If you still encounter memory issues, consider reducing the amount of data being processed or optimizing your data model. You can also use tools like DAX Studio to analyze and optimize your DAX queries.Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
You could try
Impact = VAR Successes = FILTER ( ALLEXCEPT ( Payment, Payment[[Cust_Id], Payment[Month], Payment[TransactionStatus] ), Payment[TransactionStatus] = "true" ) RETURN IF ( ISEMPTY ( Successes ), "Yes", "No" )After testing it,i've realized that the formula is putting only failed payments where impact is yes. Remember I wanted customers who had a failed payment and a successful payment in the same month.
Hi SGV ,
Here's an optimized version of your DAX formula:
Column:
Impact = VAR Success = CALCULATETABLE ( VALUES ( Payment[Cust_Id] ), Payment[TransactionStatus] = "true", ALLEXCEPT ( Payment, Payment[Month] ) ) RETURN IF ( CONTAINS ( Success, Payment[Cust_Id], Payment[Cust_Id] ), "No", "Yes" )Measure:
Impact Measure := VAR Success = CALCULATETABLE ( VALUES ( Payment[Cust_Id] ), Payment[TransactionStatus] = "true", ALLEXCEPT ( Payment, Payment[Month] ) ) VAR CurrentCustIdMonth = CONCATENATE ( SELECTEDVALUE ( Payment[Cust_Id] ), SELECTEDVALUE ( Payment[Month] ) ) RETURN IF ( CONTAINS ( Success, Payment[Cust_Id], SELECTEDVALUE ( Payment[Cust_Id] ) ), "No", "Yes" )This formula uses and to create a table of unique values with successful transactions for each month. Then, it uses the function to check if the is in the table. If it is, the formula returns "No", otherwise, it returns "Yes".
If you still encounter memory issues, consider reducing the amount of data being processed or optimizing your data model. You can also use tools like DAX Studio to analyze and optimize your DAX queries.Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you so much!
