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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
bolabuga
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
v-jiascu-msft
Employee
Employee

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
v-jiascu-msft
Employee
Employee

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.

@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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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