Multiple If statements in DAX using too many resources

[unknown917]

Hello,

I have a measure with multiple  if statements that is crashing my visual.  Is there a way to optimize the formula?  There are 7 if statements in total, not sure how to break them out into individual measures as they are dependent on each other to produce the end result:

IF(EG38=1,IF(ABS(EF38)<=$EM$2,EE38,IF(ABS(EF38)>=$EM$2,MIN(208,MAX(EE38,EB38)))),IF(EJ38<$EM$4,104,IF(EK38<$EM$4,52,IF(EL38<$EM$4,26,IF(ABS(EF38)<$EM$2,EE38,IF(ABS(EF38)>$EM$2,MAX(EE38,EB38)))))))

Any suggestions would be greatly appreciated!!!

[Anonymous]

Hi, @unknown917 

Can you provide some of the example data? And the output you expect. Please remove any sensitive data in advance.

Best Regards,

Community Support Team _Charlotte

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

[Olabanjik]

Hello,

I assume you are trying to convert an excel template to a Power BI solution.

Firstly, you can enhance readability of the excel formula to make the logic clearer:

Reference https://excel-pratique.com/en/tools/formula-beautifier 

=IF(EG38 = 1,
    IF(ABS(EF38) <= $EM$2, EE38, MIN(208, MAX(EE38, EB38))),
    IF(EJ38 < $EM$4, 104,
        IF(EK38 < $EM$4, 52,
            IF(EL38 < $EM$4, 26,
                IF(ABS(EF38) <= $EM$2, EE38, MAX(EE38, EB38))
            )
        )
    )
)

Extracting the information from the formula:

• List of Table Columns: EB38, EE38, EF38, EG38, EJ38, EK38, EL38
• List of Parameter Constant: $EM$2, $EM$4
• Literal constants used 1, 104, 52, 26

You can convert to DAX measure :

Method 1: Using IF  :

(Note: for example [EG38] will reference the relevant column in your DAX model)

DAXMeasure = 
IF (
    [EG38] = 1,
    IF (
        ABS([EF38]) <= [EM2],
        [EE38],
        MIN(208, MAX([EE38], [EB38]))
    ),
    IF (
        [EJ38] < [EM4], 
        104,
        IF (
            [EK38] < [EM4], 
            52,
            IF (
                [EL38] < [EM4], 
                26,
                IF (
                    ABS([EF38]) <= [EM2], 
                    [EE38], 
                    MAX([EE38], [EB38])
                )
            )
        )
    )
)

Method 2: Using Switch

DAXMeasure = 
SWITCH (
    TRUE(),
    [EG38] = 1 && ABS([EF38]) <= [EM2], [EE38],
    [EG38] = 1 && ABS([EF38]) > [EM2], MIN(208, MAX([EE38], [EB38])),
    [EJ38] < [EM4], 104,
    [EK38] < [EM4], 52,
    [EL38] < [EM4], 26,
    ABS([EF38]) <= [EM2], [EE38],
    ABS([EF38]) > [EM2], MAX([EE38], [EB38]),
    BLANK()  -- Default case if none of the conditions are met
)

Please mark as solution if this meets your requirement. 

[unknown917]

Thank you for the quick response!  I transposed the switch option and still get this msg when I try to apply it to a table visual:  Any other suggestions?

[Olabanjik]

Can you try creating a calculated column to compute this?

I assume the field (EB38, EE38, EF38, EG38, EJ38, EK38, EL38) are from the same table?

[unknown917]

I was actually told in another thread that I should not create a calculated column from measures.  Why is this?

[Olabanjik]

It depends. A calculated column uses runtime resources -compute power and in-memory space. If this can be optimized as much as possible within resource constraint, why not? The alternative is to rethink your semantic model and streamline your data architecture. Are you using a star-schema or snowflake model? Have you explored merging the two tables into one to reduce your join and moving between tables? All these require looking into your overall design again for opportunities to improve the performance.

[unknown917]

@Olabanjik - I converted this to a calculated column and i no longer get the resource error.  Thank you!  

[unknown917]

They are a blend from 2 tables and what if parameters born out of a need for manual intervention

[dharmendars007]

Hello @unknown917 , 

Can you please share us the column names and measure names because the IF statements you have shared looks like from excel which is using cell reference, and also in DAX make sure you use Switch function which is similar to IF which will be more readable.

If you find this helpful , please mark it as solution which will be helpful for others and Your Kudos/Likes are much appreciated!

Thank You

Dharmendar S

LinkedIN