cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
gpiero
Continued Contributor
Continued Contributor

Operators AND - OR in Nested IF

I am struggling with a silly formula but I am not able to find the proper solution. I know it should be easy .....

 

CS_Universe = 
IF (
    ZV[OrgCom] = "CUSTORG"
        && ZV[IDCust] = "491"
        && ( ZV[IDDel] = "SPD99"
        || ZV[IDDel] = "WAR99"
        || ZV[IDDel] = "DPD99"
        || ZV[IDDel] = "FOC99"
        || ZV[IDDel] = "KBD99" );
    "1CS";
    IF (
        ZV[OrgCom] = "CUSTORG"
            && ZV[IDCust] = "491"
            && (ZV[CounFinGood] = "MX"   
            || ZV[CounFinGood] = "RU"
            || ZV[CounFinGood] = "AR"
            || ZV[CounFinGood] = "CN")
            ;
        "2CS";
        IF (
            [OrgCom] = "CUSTORG"
                && [IDCust] <> "491"
                && [ECC] = "X"
                && ( ZV[IDDel] = "SPD99"
                || ZV[IDDel] = "WAR99"
                || ZV[IDDel] = "DPD99"
                || ZV[IDDel] = "FOC9"
                || ZV[IDDel] = "KBD99" );
            "3CS";
            IF (
                [OrgCom] = "CUSTORG"
                    && [IDCust] <> "491"
                    && [ECC] = BLANK ()
                    && ( ZV[IDDel] = "SPD99"
                    || ZV[IDDel] = "WAR99"
                    || ZV[IDDel] = "DPD99"
                    || ZV[IDDel] = "FOC99"
                    || ZV[IDDel] = "KBD99" );
                "4CS";
                BLANK ()
            )
        )
    )
)

This formula work perfectly except the 2nd if that should write "2CS".  I also moved it bottom just to test.

 

Then I added a new calculated column putting only the following and I have got the right result.

IF (
        ZV[OrgCom] = "CUSTORG"
            && ZV[IDCust] = "491"
            && (ZV[CounFinGood] = "MX"   
            || ZV[CounFinGood] = "RU"
            || ZV[CounFinGood] = "AR"
            || ZV[CounFinGood] = "CN")
            ;
        "2CS";

 

So syntax is correct and the order of operators too, otherwise the first IF above should not work.

Please is there someone who could explain me how to fix it?

Thanks

If I can...
1 ACCEPTED SOLUTION
gpiero
Continued Contributor
Continued Contributor

@Framet

Many thanks for your advices.

I tried to split in 4 different columns. Doing  that I realized that 1st IF should have a differente logic:

 

IF (
    ZV[OrgCom] = "CUSTORG"
        && ZV[IDCust] = "491"
        && ( ZV[IDDel] = "SPD99"
        || ZV[IDDel] = "WAR99"
        || ZV[IDDel] = "DPD99"
        || ZV[IDDel] = "FOC99"
        || ZV[IDDel] = "KBD99" )
        && (ZV[CounFinGood] <> "MX"   
        && ZV[CounFinGood] <> "RU"
        && ZV[CounFinGood] <> "AR"
        && ZV[CounFinGood] <> "CN");

    "1CS";

 

 

Now I have changed the orginal long formula and  it is ok.

 

Thanks again.

If I can...

View solution in original post

8 REPLIES 8
Framet
Resolver II
Resolver II

Without going into the what and why of this query. Can you advise on the returned value for SC_Universe you are seeing when you believe the second nested IF should be catching the result? Eg when you are expecting "2CS".

Kind Regards

 

Thomas

 

 

gpiero
Continued Contributor
Continued Contributor

I am not sure to caught your point. Do you mean which condition should be satisfied?

If I can...

You mention the formula works perfectly except the second IF. I'm assuming at somepoint you are expecting the result  "2CS" but getting something else. What is that something else?

gpiero
Continued Contributor
Continued Contributor

I get nothing.

While using the calculation of the 2nd column added for test COUNTROWS finds 91 rows and tha value is ok due I checked it via ERP.

If I can...

Whilst the second column you mention the formula is incomplete if we assume it simply returns blank when not "2CS" then I'm with you in so far as I can't see how that can work yet the first calculated column believes the value isn't a match and falls out as blank().

 

I also agree the structure of the formula whilst not idea should achieve what you describe you want.

 

Have you checked an individual row in the table to see what the first column returns for a record that matches:

[OrgCom] = "CUSTORG"
            && ZV[IDCust] = "491"
            && ZV[CounFinGood] = "MX"   

 You could of course create 4 columns, one for each IF statement and a 5th column that concatonates the result. (assuming no record can match more than one IF statement, if the order is important then simply create the 5th column to return the first when not blank else the second and so on. 

Not efficient in terms of storage but should have the same effect.

gpiero
Continued Contributor
Continued Contributor

Sorry,

I have copied it partially.

 

IF (
        ZV[OrgCom] = "CUSTORG"
            && ZV[IDCust] = "149"
            && (ZV[CounFinGood] = "MX"   
            || ZV[CounFinGood] = "RU"
            || ZV[CounFinGood] = "AR"
            || ZV[CounFinGood] = "CN")
            ;
        "2CS";
        BLANK();
)
If I can...
gpiero
Continued Contributor
Continued Contributor

@Framet

Many thanks for your advices.

I tried to split in 4 different columns. Doing  that I realized that 1st IF should have a differente logic:

 

IF (
    ZV[OrgCom] = "CUSTORG"
        && ZV[IDCust] = "491"
        && ( ZV[IDDel] = "SPD99"
        || ZV[IDDel] = "WAR99"
        || ZV[IDDel] = "DPD99"
        || ZV[IDDel] = "FOC99"
        || ZV[IDDel] = "KBD99" )
        && (ZV[CounFinGood] <> "MX"   
        && ZV[CounFinGood] <> "RU"
        && ZV[CounFinGood] <> "AR"
        && ZV[CounFinGood] <> "CN");

    "1CS";

 

 

Now I have changed the orginal long formula and  it is ok.

 

Thanks again.

If I can...

Hi @gpiero,


From your description,  you have solved this issue, right? If that is the case, you can accept  your reply as solution, that way, other community members would benefit from your solution.

Thanks,
Lydia Zhang

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

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Show episode 9

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 25

Ted's Dev Camp - August 25, 2022

Watch Session 25 of Ted's Dev Camp.

Top Solution Authors