cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper V
Helper V

[ calculated column and "no memory available to complete the operation"]

Hello everyone, 

 

I cant share the database then i will post the calculated column that im trying to run and its not working because of memory amount. (i currently have 16GB of RAM).

 

First i will always work with 2 years, in this case i have 2016 and 2017.

 

The whole idea of this column is compare my current revenue table against a service table and, for each row context, IF a client had any service between 13 months period (for a certain set of services) i want blank as result. 

 

Following the same idea, in the middle of the IF chain, i want blank as result if the client had any service in a period of 50 day of the current monthyear or at the current monthyear of the lastyear (for a certain set of services).

 

In the end of the IF chain, im consisting only clients that had "ZERO" services, and then if they exist on the 2 monthyears compared, then i want their "value". 

 

I Know that my cardinalitie is huge problem here, because my revenues[client] column has 1,2 millions unique values.

 

The question is:

Is there another way i can construct a similar logic that will have less impact on memory?? i dont know another way to do this, or if i know, im not seeing it, without using the IF chain. Then again, im not sure if the "IF chain" is the major impact.

 

 

CALCULATED COLUMN = 
IF(  
    REVENUES[YEAR] = 2016 
    && COUNTAX(
        FILTER(
            SERVICES;
            SERVICES[CLIENT] = REVENUES[CLIENT] 
            && SERVICES[TYPE] <> 23
            && SERVICES[TYPE] <> 11
            && SERVICES[SUBTYPE] <> 100007
            && SERVICES[SUBTYPE] <> 100008
            && SERVICES[DATE] >= REVENUES[DATE] - 40
            && SERVICES[DATE] <= REVENUES[DATE] + 375);
        SERVICES[CLIENT]) > 0;
    BLANK();
IF(
    REVENUES[YEAR] = 2017
    && COUNTAX(
        FILTER(
            SERVICES;
            SERVICES[CLIENT] = REVENUES[CLIENT] 
            && SERVICES[TYPE] <> 23
            && SERVICES[TYPE] <> 11
            && SERVICES[SUBTYPE] <> 100007
            && SERVICES[SUBTYPE] <> 100008
            && SERVICES[DATE] >= REVENUES[DATE] - 405
            && SERVICES[DATE] <= REVENUE[DATE] + 10);
        SERVICES[CLIENT]) > 0;
    BLANK();
IF(
    REVENUES[YEAR] = 2016 
    && COUNTAX(
        FILTER(
            SERVICES;
            SERVICES[CLIENT] = REVENUES[CLIENT]
            && (SERVICES[SUBTYPE] = 100007 || SERVICES[SUBTYPE] = 100008 || SERVICES[TYPE] = 23 || SERVICES[TYPE] = 11)
            && SERVICES[DATE] >= REVENUES[DATE] - 40
            && SERVICES[DATE] <= REVENUE[DATE] + 10);
        SERVICES[CLIENT]) > 0;
        BLANK();
IF(
    REVENUES[YEAR] = 2016 
    && COUNTAX(
        FILTER(
            SERVICES;
            SERVICES[CLIENT] = REVENUES[CLIENT]
            && (SERVICES[SUBTYPE] = 100007 || SERVICES[SUBTYPE] = 100008 || SERVICES[TYPE] = 23 || SERVICES[TYPE] = 11)
            && SERVICES[DATE] >= REVENUES[DATE] + 325 
            && SERVICES[DATE] <= REVENUES[DATE] + 375);
        SERVICES[CLIENT]) > 0;
        BLANK();
IF(
    REVENUES[YEAR] = 2017
    && COUNTAX(
        FILTER(
            SERVICES;
            SERVICES[CLIENT] = REVENUES[CLIENT]
            && (SERVICES[SUBTYPE] = 100007 || SERVICES[SUBTYPE] = 100008 || SERVICES[TYPE] = 23 || SERVICES[TYPE] = 11)
            && SERVICES[DATE] >= REVENUES[DATE] - 40
            && SERVICES[DATE] <= REVENUE[DATE] + 10);
        SERVICES[CLIENT]) > 0;
        BLANK();
IF(
    REVENUES[YEAR] = 2017
    && COUNTAX(
        FILTER(
            SERVICES;
            SERVICES[CLIENT] = REVENUES[CLIENT]
            && (SERVICES[SUBTYPE] = 100007 || SERVICES[SUBTYPE] = 100008 || SERVICES[TYPE] = 23 || SERVICES[TYPE] = 11)
            && SERVICES[DATE] >= REVENUES[DATE] - 405
            && SERVICES[DATE] <= REVENUES[DATE] - 355);
        SERVICES[CLIENT]) > 0;
        BLANK();        
IF(
    REVENUES[YEAR] = 2016;
    LOOKUPVALUE(
        REVENUES[CLIENT];
        REVENUES[YEAR];REVENUES[YEAR] + 1;
        REVENUES[MONTHNAME];REVENUES[MONTHNAME];
        REVENUES[CLIENT];REVENUES[CLIENT]);
    LOOKUPVALUE(
        REVENUES[CLIENT];
        REVENUES[YEAR];REVENUES[YEAR] - 1;
        REVENUES[MONTHNAME];REVENUES[MONTHNAME];
        REVENUES[CLIENT];REVENUES[CLIENT]))))))))
1 ACCEPTED SOLUTION
Microsoft
Microsoft

Hi @bolabuga,

 

There are some ways that you can optimize your formula.

Possible causes:

1. "Filter" will create a row context. In other words, there will be a new virtual table. The conditions of "Filter" will be compared row by row in that table. 

2. "Countax" will iterate the "table" parameter row by row. Actually, count the rows of the filtered table is enough. Use "Countrows" instead.

3. Some conditions of IF are duplicate. The formula has to evaluate many time.

Possible solutions.

1. Do the judgement in the table Service.

tag 1 =
IF (
    SERVICES[TYPE] <> 23
        && SERVICES[TYPE] <> 11
        && SERVICES[SUBTYPE] <> 100007
        && SERVICES[SUBTYPE] <> 100008;
    1;
    0
)

Then the calculation will be decreased. The formula could be:

CALCULATED COLUMN =
IF (
    REVENUES[YEAR] = 2016
        && COUNTAX (
            FILTER (
                SERVICES;
                SERVICES[CLIENT] = REVENUES[CLIENT]
                    && SERVICES[TAG 1] = 1
                    && SERVICES[DATE]
                    >= REVENUES[DATE] - 40
                    && SERVICES[DATE]
                    <= REVENUES[DATE] + 375
            );
            SERVICES[CLIENT]
        )
            > 0;
    BLANK ();
    ... ...
)

2. The result for many IF statement is blank. So whatever the condition is, let's union them. Actually,

( SERVICES[TYPE] <> 23 && SERVICES[TYPE] <> 11 && SERVICES[SUBTYPE] <> 100007 && SERVICES[SUBTYPE] <> 100008) 

union this condition 

( SERVICES[SUBTYPE] = 100007 || SERVICES[SUBTYPE] = 100008 || SERVICES[TYPE] = 23 || SERVICES[TYPE] = 11)

means the whole table.

Maybe you can try this:

CALCULATED COLUMN =
IF (
    REVENUES[YEAR] = 2016
        && COUNTROWS (
            FILTER (
                SERVICES;
                SERVICES[CLIENT] = REVENUES[CLIENT]
                    && SERVICES[TAG 1] = 1
                    && SERVICES[DATE]
                    > REVENUES[DATE] + 10
                    && SERVICES[DATE]
                    < REVENUES[DATE] + 325
            )
        )
            > 0;
    BLANK ();
    IF (
        REVENUES[YEAR] = 2017
            && COUNTROWS (
                FILTER (
                    SERVICES;
                    SERVICES[CLIENT] = REVENUES[CLIENT]
                        && SERVICES[TAG 1] = 1
                        && SERVICES[DATE]
                        > REVENUES[DATE] - 355
                        && SERVICES[DATE]
                        < REVENUE[DATE] - 40
                )
            )
                > 0;
        BLANK ();
        IF (
            REVENUES[YEAR] = 2016
                && COUNTROWS (
                    FILTER (
                        SERVICES;
                        SERVICES[CLIENT] = REVENUES[CLIENT]
                            && ( SERVICES[DATE]
                            >= REVENUES[DATE] - 40
                            && SERVICES[DATE]
                            <= REVENUE[DATE] + 10 )
                            || ( SERVICES[DATE]
                            >= REVENUES[DATE] + 325
                            && SERVICES[DATE]
                            <= REVENUES[DATE] + 375 )
                    )
                )
                    > 0;
            BLANK ();
            IF (
                REVENUES[YEAR] = 2017
                    && COUNTROWS (
                        FILTER (
                            SERVICES;
                            SERVICES[CLIENT] = REVENUES[CLIENT]
                                && ( SERVICES[DATE]
                                >= REVENUES[DATE] - 40
                                && SERVICES[DATE]
                                <= REVENUE[DATE] + 10 )
                                || ( SERVICES[DATE]
                                >= REVENUES[DATE] - 405
                                && SERVICES[DATE]
                                <= REVENUES[DATE] - 355 )
                        )
                    )
                        > 0;
                BLANK ();
                IF (
                    REVENUES[YEAR] = 2016;
                    LOOKUPVALUE (
                        REVENUES[CLIENT];
                        REVENUES[YEAR]; REVENUES[YEAR] + 1;
                        REVENUES[MONTHNAME]; REVENUES[MONTHNAME];
                        REVENUES[CLIENT]; REVENUES[CLIENT]
                    );
                    LOOKUPVALUE (
                        REVENUES[CLIENT];
                        REVENUES[YEAR]; REVENUES[YEAR] - 1;
                        REVENUES[MONTHNAME]; REVENUES[MONTHNAME];
                        REVENUES[CLIENT]; REVENUES[CLIENT]
                    )
                )
            )
        )
    )
)

 

Best Regards!

Dale

 

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
Microsoft
Microsoft

Hi @bolabuga,

 

There are some ways that you can optimize your formula.

Possible causes:

1. "Filter" will create a row context. In other words, there will be a new virtual table. The conditions of "Filter" will be compared row by row in that table. 

2. "Countax" will iterate the "table" parameter row by row. Actually, count the rows of the filtered table is enough. Use "Countrows" instead.

3. Some conditions of IF are duplicate. The formula has to evaluate many time.

Possible solutions.

1. Do the judgement in the table Service.

tag 1 =
IF (
    SERVICES[TYPE] <> 23
        && SERVICES[TYPE] <> 11
        && SERVICES[SUBTYPE] <> 100007
        && SERVICES[SUBTYPE] <> 100008;
    1;
    0
)

Then the calculation will be decreased. The formula could be:

CALCULATED COLUMN =
IF (
    REVENUES[YEAR] = 2016
        && COUNTAX (
            FILTER (
                SERVICES;
                SERVICES[CLIENT] = REVENUES[CLIENT]
                    && SERVICES[TAG 1] = 1
                    && SERVICES[DATE]
                    >= REVENUES[DATE] - 40
                    && SERVICES[DATE]
                    <= REVENUES[DATE] + 375
            );
            SERVICES[CLIENT]
        )
            > 0;
    BLANK ();
    ... ...
)

2. The result for many IF statement is blank. So whatever the condition is, let's union them. Actually,

( SERVICES[TYPE] <> 23 && SERVICES[TYPE] <> 11 && SERVICES[SUBTYPE] <> 100007 && SERVICES[SUBTYPE] <> 100008) 

union this condition 

( SERVICES[SUBTYPE] = 100007 || SERVICES[SUBTYPE] = 100008 || SERVICES[TYPE] = 23 || SERVICES[TYPE] = 11)

means the whole table.

Maybe you can try this:

CALCULATED COLUMN =
IF (
    REVENUES[YEAR] = 2016
        && COUNTROWS (
            FILTER (
                SERVICES;
                SERVICES[CLIENT] = REVENUES[CLIENT]
                    && SERVICES[TAG 1] = 1
                    && SERVICES[DATE]
                    > REVENUES[DATE] + 10
                    && SERVICES[DATE]
                    < REVENUES[DATE] + 325
            )
        )
            > 0;
    BLANK ();
    IF (
        REVENUES[YEAR] = 2017
            && COUNTROWS (
                FILTER (
                    SERVICES;
                    SERVICES[CLIENT] = REVENUES[CLIENT]
                        && SERVICES[TAG 1] = 1
                        && SERVICES[DATE]
                        > REVENUES[DATE] - 355
                        && SERVICES[DATE]
                        < REVENUE[DATE] - 40
                )
            )
                > 0;
        BLANK ();
        IF (
            REVENUES[YEAR] = 2016
                && COUNTROWS (
                    FILTER (
                        SERVICES;
                        SERVICES[CLIENT] = REVENUES[CLIENT]
                            && ( SERVICES[DATE]
                            >= REVENUES[DATE] - 40
                            && SERVICES[DATE]
                            <= REVENUE[DATE] + 10 )
                            || ( SERVICES[DATE]
                            >= REVENUES[DATE] + 325
                            && SERVICES[DATE]
                            <= REVENUES[DATE] + 375 )
                    )
                )
                    > 0;
            BLANK ();
            IF (
                REVENUES[YEAR] = 2017
                    && COUNTROWS (
                        FILTER (
                            SERVICES;
                            SERVICES[CLIENT] = REVENUES[CLIENT]
                                && ( SERVICES[DATE]
                                >= REVENUES[DATE] - 40
                                && SERVICES[DATE]
                                <= REVENUE[DATE] + 10 )
                                || ( SERVICES[DATE]
                                >= REVENUES[DATE] - 405
                                && SERVICES[DATE]
                                <= REVENUES[DATE] - 355 )
                        )
                    )
                        > 0;
                BLANK ();
                IF (
                    REVENUES[YEAR] = 2016;
                    LOOKUPVALUE (
                        REVENUES[CLIENT];
                        REVENUES[YEAR]; REVENUES[YEAR] + 1;
                        REVENUES[MONTHNAME]; REVENUES[MONTHNAME];
                        REVENUES[CLIENT]; REVENUES[CLIENT]
                    );
                    LOOKUPVALUE (
                        REVENUES[CLIENT];
                        REVENUES[YEAR]; REVENUES[YEAR] - 1;
                        REVENUES[MONTHNAME]; REVENUES[MONTHNAME];
                        REVENUES[CLIENT]; REVENUES[CLIENT]
                    )
                )
            )
        )
    )
)

 

Best Regards!

Dale

 

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

@v-jiascu-msft

 

Reporting back, i had free time to study and replace the calculated column using all your insights and everything worked.

 

Testing in a smaller file (version of just 1 neighborhood) and the new calculated column returned the same results of the old version. And liberating all clients in query editor went pretty well, everything updated without problem.

 

Thks again Dale. 

@v-jiascu-msft

 

Really thks man for the detailed explanation, its was really nice insights.

 

I can understand and see now that your suggestions will have less impact on memory. As soon as im out of the analytic period of the month i will rewrite my calculated column and will run some new tests on the table!!!

 

Again really thks for all the great reply!!!

 

 

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors