topic Re: Multiple If statements in DAX using too many resources in DAX Commands and Tips

Multiple If statements in DAX using too many resources

unknown917 — Sat, 21 Sep 2024 11:12:17 GMT

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!!!

Re: Multiple If statements in DAX using too many resources

dharmendars007 — Sat, 21 Sep 2024 11:28:25 GMT

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

Re: Multiple If statements in DAX using too many resources

Olabanjik — Sat, 21 Sep 2024 12:17:16 GMT

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.

Re: Multiple If statements in DAX using too many resources

unknown917 — Sat, 21 Sep 2024 20:00:45 GMT

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?

Re: Multiple If statements in DAX using too many resources

Olabanjik — Sat, 21 Sep 2024 23:10:38 GMT

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?

Re: Multiple If statements in DAX using too many resources

unknown917 — Sat, 21 Sep 2024 23:59:01 GMT

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

Re: Multiple If statements in DAX using too many resources

Anonymous — Mon, 23 Sep 2024 01:37:19 GMT

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.

Re: Multiple If statements in DAX using too many resources

unknown917 — Mon, 23 Sep 2024 18:24:09 GMT

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

Re: Multiple If statements in DAX using too many resources

unknown917 — Tue, 24 Sep 2024 21:04:40 GMT

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

Re: Multiple If statements in DAX using too many resources

Olabanjik — Thu, 26 Sep 2024 22:12:22 GMT

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.