IF Column is blank, then use another IF statement in created column

[OPS-MLTSD]

Hello, I am trying to create a new column called "New Ministry Role" based on an existing column called "Ministry Role" in my table. There is one blank value in the "Ministry Role" column, which is why I am creating this new column. I would like this new created column to return the exact values of the "Ministry Role" column, however, in cases where there is a blank, I want my new created column to use this statement: New Ministry Role = IF(OR('CA usage'[MINISTRY ROLE] = "MCCSS", 'CA usage'[SOURCE_SYSTEM_CODE] = "SAMS"), "MCCSS", "MLTSD")

I tried to do that, but as you can see from the screenshot, the new column changes the Ministry role value from MLTSD to MCCSS. I was wondering if someone could help me fix this code so that the "New Ministry Role"  column returns the exact same values of the "Ministry Role" column but in cases where there is a blank, it uses the IF statement  (IF(OR('CA usage'[MINISTRY ROLE] = "MCCSS", 'CA usage'[SOURCE_SYSTEM_CODE] = "SAMS"), "MCCSS", "MLTSD")) to fill in the blank. Thank you.

[darentengmfs]

Hi @OPS-MLTSD 

Try modifying your formula to this. Use AND statement instead of OR.
 =IF(AND('CA usage'[MINISTRY ROLE] = "MCCSS", 'CA usage'[SOURCE_SYSTEM_CODE] = "SAMS"), "MCCSS", "MLTSD")

[OPS-MLTSD]

If I use an AND statement, I am telling power BI tha if MInistry Role AND Source System  Code is SAMS, then give me "MCCSS" otherwise give me "MLTSD" which is incorrect. I would like new created column to return the exact same values of the "Ministry Role" column but in cases where there is a blank, it uses the IF/OR statement above

[darentengmfs]

@OPS-MLTSD 

When you use OR Statement, it means that if either conditions are met, then MCCSS.

What that means is:

if [SOURCE_SYSTEM_CODE] is "SAMS", it will populate your new column as MCCSS

if [MINISTRY ROLE] is "MCCSS", it will populate your new column as MCCSS

So using your logic, as long as your [SOURCE_SYSTEM_CODE] is "SAMS", regardless of [MINISTRY ROLE], you will get MCCSS. Likewise, if your [MINISTRY ROLE] is "MCCSS", regardless of [SOURCE_SYSTEM_CODE], it will give you "MCCSS".

Let's take your last line in your screenshot as an example. Since your [SOURCE_SYSTEM_CODE] is "SAMS", the condition has already been met because of the OR statement, so it will give you "MCCSS", even when your [MINISTRY ROLE] is "MLTSD".

Therefore, you should use the ISBLANK statement.

=IF(ISBLANK([MINISTRY ROLE]), IF('CA usage'[SOURCE_SYSTEM_CODE] = "SAMS"), "MCCSS", "MLTSD"), [MINISTRY ROLE])

What this will do is that it will check [MINISTRY ROLE]. If it is blank, then it will apply the formula, and if it's not blank, it will give you [MINISTRY ROLE].

[OPS-MLTSD]

this is the most helpful explanation I have received on power bi. Thank you! So would this work?

=IF(ISBLANK([MINISTRY ROLE]), IF(AND('CA usage'[MINISTRY ROLE] = "MCCSS", 'CA usage'[SOURCE_SYSTEM_CODE] = "SAMS"), "MCCSS", "MLTSD"), [MINISTRY ROLE]) 

[darentengmfs]

@OPS-MLTSD 

If that's the logic you are going for, I believe it will work. What I will do is, apply the formula, then filter your old [MINISTRY ROLE] column = blank, and check if the formula worked.

[OPS-MLTSD]

=IF(ISBLANK([MINISTRY ROLE]), IF(AND('CA usage'[MINISTRY ROLE] = "MCCSS", 'CA usage'[SOURCE_SYSTEM_CODE] = "SAMS"), "MCCSS", "MLTSD"), [MINISTRY ROLE]) 

so basically what this formula above is saying is this:

IF Ministry Role is MCCSS and IF Surce System Code is SAMS, then give me "MCCSS" otherwise give me "MLTSD" but first check the old [MINISTRY ROLE] column, if that column is already filled, give me the exact value in that column. Is my assumption correct?

[darentengmfs]

@OPS-MLTSD 

Yes that is right, check if it's blank, if yes, apply your formula, if it's not blank, refer back to [MINISTRY ROLE].

However, I think the red part is redundant:

=IF(ISBLANK([MINISTRY ROLE]), IF(AND('CA usage'[MINISTRY ROLE] = "MCCSS", 'CA usage'[SOURCE_SYSTEM_CODE] = "SAMS"), "MCCSS", "MLTSD"), [MINISTRY ROLE]) 
Because the function is already checking for blank values first. So if it's blank, [MINISTRY ROLE] will not have any values at all, so there is no point putting that part in.

If you do put it in, since it is an AND statement, you will not get the result you want because the data you have is already blank, it is trying to look for [MINISTRY ROLE]="MCCSS" AND [SOURCE_SYSTEM_CODE] ="SAMS", which it will never find because [MINISTRY ROLE] is blank.

[OPS-MLTSD]

Ah I see, you are right that makes sense. Sorry for so many questions but I tried the above statement you posted: =IF(ISBLANK([MINISTRY ROLE]), IF('CA usage'[SOURCE_SYSTEM_CODE] = "SAMS"), "MCCSS", "MLTSD"), [MINISTRY ROLE]) 

which makes sense to me now, and I understand why I am getting this result:

But you see where it says "MLTSD" in the second row? It shouls say "NO Ministry" since [CLIENT TYPE] is "NO USER", so is it possible to add another condition to the dax somethign like?:

=IF(ISBLANK([MINISTRY ROLE]), IF(AND('CA usage'[MINISTRY ROLE] = "MCCSS", 'CA usage'[SOURCE_SYSTEM_CODE] = "SAMS"), "MCCSS",)IF('CA usage'[CLIENT TYPE] = "NO USER"), "NO MINNISTRY", "MLTSD"), [MINISTRY ROLE])  

[darentengmfs]

@OPS-MLTSD 

How many different Client Types do you have?

[OPS-MLTSD]

I have 5 different client types: these are all of them:

1. MCCSS NOT REFERRED

2. MCCSS REFERRED

3. MLTSD NOT REFERRED

4. NO USER

5. UNHAPPY PATH

[darentengmfs]

@OPS-MLTSD 

What do you want each of them to show if Client Type is not blank?

[OPS-MLTSD]

So if Client Type is not blank, then [New Ministry] should be:

[CLIENT TYPE]                       [NEW MINISTRY]

MCCSS NOT REFERRED =     MCCSS

MCCSS REFERRED =              MCCSS

MLTSD NOT REFERRED =      MLTSD

NO USER =                            NO MINISTRY

UNHAPPY PATH =                 MCCSS

[darentengmfs · Accepted solution]

@OPS-MLTSD 

Just in case your client type is either "" or blank,

New Ministry =
IF(OR(ISBLANK([CLIENT TYPE]),[CLIENT TYPE]=""),IF([SOURCE_SYSTEM_CODE]="SAMS","MCCSS",""),

SWITCH([CLIENT TYPE],"MCCSS NOT REFERRED","MCCSS","MCCSS REFERRED","MCCSS","MLTSD NOT REFERRED","MLTSD","NO USER","NO MINISTRY","UNHAPPY PATH","MCCSS"))

What this will do is it will search Client Type for either Blank or "". If it is, then it will look for Source System Code. So if Client Type = Blank or "" and if Source System Code = "SAMS", then it will give you "MCCSS". If it is not blank or "", it will look at Client Type, where if Client Type ="MCCSS NOT REFERRED", then it will give you "MCCSS" and so on. SWITCH works similarly as IF function.

[OPS-MLTSD]

This is great! So can I do this?:

New Ministry = IF(ISBLANK([MINISTRY ROLE]),
IF(OR(ISBLANK([CLIENT TYPE]),[CLIENT TYPE]=""),IF([SOURCE_SYSTEM_CODE]="SAMS","MCCSS",""),
SWITCH([CLIENT TYPE],"MCCSS NOT REFERRED","MCCSS","MCCSS REFERRED","MCCSS","MLTSD NOT REFERRED","MLTSD","NO USER","NO MINISTRY","UNHAPPY PATH","MCCSS")), [MINISTRY ROLE])

Since my primary driver is the original [MINISTRY ROLE] column, if this column is blank, then it does the above function that you have written?

[darentengmfs]

@OPS-MLTSD 

Yes, I believe that will work. Please give it a try and let me know.

[OPS-MLTSD]

it worked, thank you, really appreciated your help on this!