Getting not enough memory error on powerbi

[SGV]

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.

[v-tangjie-msft · Accepted solution]

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. 

[SGV]

Thank you so much!

[johnt75]

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

[SGV]

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.