Multiple nested IF statements

[tomekm]

Hello,

I'm trying to write a dax formula for a calculated column that contains multiple IF statements but I'm getting stuck with syntax errors. This is how the formula looks like in excel, but it's not as straight forward in DAX for me, with the multiple nested statements. Here's the excel formula that I'm trying to write in dax:

Status = IF(column Total > 0, IFS(AND(column A > 0, column B > 0, column C > 1, column D > 1),IF(column Customer Type ="OK","Primary","Secondary"), AND(column A > 0, column C > 1,column D > 0), IF(Customer Type = "Upcoming","Tertiary","Advanced"),TRUE,IF(Customer Type = "Other","Basic","Next")),"No Access")

To note: columns A, B, C, D are each a count distinct of values, that have a condition such that:
Column A, B, C, D = count distinct of 'occurences', given that status in column X = "good".

My data is quite simple and looks like this:

A	B	C	D	Total	Customer type	Status:
1	0	2	5	8

Any help is appreciated!

Cheers.

[v-karpurapud]

Hi @tomekm 

Could you please confirm if your query have been resolved the solution provided by @SamsonTruong , @kpost and @maruthisp ? If they have, kindly mark the helpful response and accept it as the solution. This will assist other community members in resolving similar issues more efficiently.

Thank you.

[maruthisp]

Hi @kpost ,

Can you try below DAX:
Status =
IF (
[Total] > 0,
IF (
[A] > 0 && [B] > 0 && [C] > 1 && [D] > 1,
IF ( [Customer Type] = "OK", "Primary", "Secondary" ),
IF (
[A] > 0 && [C] > 1 && [D] > 0,
IF ( [Customer Type] = "Upcoming", "Tertiary", "Advanced" ),
IF ( [Customer Type] = "Other", "Basic", "Next" )
)
),
"No Access"
)

If this reply helped solve your problem, please consider clicking "Accept as Solution" so others can benefit too. And if you found it useful, a quick "Kudos" is always appreciated, thanks! 

Best Regards, 

Maruthi 

LinkedIn - http://www.linkedin.com/in/maruthi-siva-prasad/ 

X            -  Maruthi Siva Prasad - (@MaruthiSP) / X

[kpost]

In DAX, a Switch statement is much better than an IF statements for situations like this where the syntax becomes difficult to disentangle.

It would be something like this:

Status = 
          SWITCH(
                     TRUE(),
            <Logic A>, <Return value if Logic A is true>,
            <Logic B>, <Return value if Logic B is true>,
            <Default return value if Logic A and Logic B are both false>
           )

Remember, while writing, to keep in mind that it will stop evaluating and return the return value on the first line whose logic evaluates to true.  If Logic A and Logic B are both true for a certain row, they will both return the return value for Logic A.

So the precise order matters sometimes for getting the correct return values, but also you can thoughtfully put them in a specific order such that the whole thing is more efficient by ordering the situations in the anticipated frequency that each condition will be fulfilled.  (If you think Logic B is the one that will be evaluated as true in 90% of cases, put it first, so that the code for Logic A does not have to evaluate every single time, unnecessarily)

///Mediocre Power BI Advice, but it's free///

[SamsonTruong · Accepted solution]

Hi @tomekm ,

Please try the following DAX for your calculated column and replace 'Table' with the name of your table:

Status = 
IF (
    'Table'[Total] > 0,
    SWITCH (
        TRUE(),
        'Table'[A] > 0 && 'Table'[B] > 0 && 'Table'[C] > 1 && 'Table'[D] > 1,
            IF('Table'[Customer Type] = "OK", "Primary", "Secondary"),
        'Table'[A] > 0 && 'Table'[C] > 1 && 'Table'[D] > 0,
            IF('Table'[Customer Type] = "Upcoming", "Tertiary", "Advanced"),
        'Table'[Customer Type] = "Other", "Basic",
        "Next"
    ),
    "No Access"
)

  
The SWITCH() function in DAX acts the same as nested IF() statements. However, it is more performant and easier to manage.

If this helped, please mark it as the solution so others can benefit too. And if you found it useful, kudos are always appreciated.

Thanks,

Samson