Conditional Nested IF Statement

[Sidzx6r]

Hello All

How do i write nested if statement column condition to replace blank values with "NoProgress" when schedule date is blank or null.

Calendar date column from calendar auto table has all days in a month. Calendar table &  schedule data table are connected by Date column , & schedule date table doesnt have all days. 

Attaching sample screenshot and sampel data.

Calendar Date	Scheduled Date	Type	Product	Conditional
6/1/2024 0:00
6/2/2024 0:00
6/3/2024 0:00	6/3/2024 0:00	A	Cotton	InProgress
6/3/2024 0:00	6/3/2024 0:00	A	County	InProgress
6/3/2024 0:00	6/3/2024 0:00	A	Crop	InProgress
6/3/2024 0:00	6/3/2024 0:00	A	Fats	InProgress
6/3/2024 0:00	6/3/2024 0:00	A	Grain	InProgress
6/4/2024 0:00
6/5/2024 0:00	6/5/2024 0:00	A	Broiler	InProgress
6/5/2024 0:00	6/5/2024 0:00	A	Dairy	InProgress
6/6/2024 0:00
6/7/2024 0:00	6/7/2024 0:00	A	Peanut	InProgress
6/8/2024 0:00
6/9/2024 0:00
6/10/2024 0:00	6/10/2024 0:00	A	Crop	InProgress
6/11/2024 0:00
6/12/2024 0:00	6/12/2024 0:00	A	Broiler	InProgress
6/12/2024 0:00	6/12/2024 0:00	A	Crop	InProgress
6/13/2024 0:00
6/14/2024 0:00	6/14/2024 0:00	A	Peanut	InProgress
6/14/2024 0:00	6/14/2024 0:00	A	Turkey	InProgress
6/15/2024 0:00
6/16/2024 0:00
6/17/2024 0:00	6/17/2024 0:00	A	Crop	InProgress
6/17/2024 0:00	6/17/2024 0:00	A	Potato	InProgress
6/18/2024 0:00
6/19/2024 0:00	6/19/2024 0:00	A	HOLIDAY	NoWork
6/20/2024 0:00	6/20/2024 0:00	A	Broiler	InProgress
6/20/2024 0:00	6/20/2024 0:00	A	Livestock	InProgress
6/21/2024 0:00	6/21/2024 0:00	A	Cattle	InProgress
6/21/2024 0:00	6/21/2024 0:00	A	Milk	InProgress
6/21/2024 0:00	6/21/2024 0:00	A	Peanut	InProgress
6/22/2024 0:00
6/23/2024 0:00
6/24/2024 0:00	6/24/2024 0:00	A	Chickens	InProgress
6/24/2024 0:00	6/24/2024 0:00	A	Crop	InProgress
6/24/2024 0:00	6/24/2024 0:00	A	Poultry	InProgress
6/25/2024 0:00	6/25/2024 0:00	A	Cold	InProgress
6/26/2024 0:00	6/26/2024 0:00	A	Broiler	InProgress
6/26/2024 0:00	6/26/2024 0:00	A	Peanut	InProgress
6/27/2024 0:00	6/27/2024 0:00	A	Hogs	InProgress
6/28/2024 0:00	6/28/2024 0:00	A	Acreage	InProgress
6/28/2024 0:00	6/28/2024 0:00	A	Agricultural	InProgress
6/28/2024 0:00	6/28/2024 0:00	A	Egg	InProgress
6/28/2024 0:00	6/28/2024 0:00	A	Grain	InProgress
6/28/2024 0:00	6/28/2024 0:00	A	Peanut	InProgress
6/28/2024 0:00	6/28/2024 0:00	A	Rice	InProgress
6/28/2024 0:00	6/28/2024 0:00	B	Acreage
6/29/2024 0:00
6/30/2024 0:00

[rajendraongole1 · Accepted solution]

Hi @Sidzx6r - Create a new column as below to check on scheduled date.

Conditional =

IF (

    ISBLANK(ScheduledT[Scheduled Date]),

    "NoProgress",

    IF (

        ScheduledT[Type] = "A" && ScheduledT[Product] <> "HOLIDAY",

        "InProgress",

        ""

    )

)

you can join the calendar and schedule tables based on date column 

Create merged table reflects the correct structure and that the conditional column shows "NoProgress" for dates without scheduled entries. 

Hope it works. 

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

[MNedix · Accepted solution]

@Sidzx6r 
Alternatively to @rajendraongole1 's solution, you can use a Switch function as below; the advantage of the Switch function is lower complexity and highly increased scalability (multiple conditions with ease of configuraiton):

New Conditional = 
SWITCH(
    TRUE(),
    ISBLANK(Sheet1[Scheduled Date]),"InProgress",
    Sheet1[Scheduled Date]="null","InProgress",
    Sheet1[Conditional]
)

In your example, you don't really need the "null" statement but I put it there just in case you encounter it in your data model. You can use as many conditions as you need (including && operators, for example for the Type B Products).

Best,

[MNedix · Accepted solution]

@Sidzx6r 
Alternatively to @rajendraongole1 's solution, you can use a Switch function as below; the advantage of the Switch function is lower complexity and highly increased scalability (multiple conditions with ease of configuraiton):

New Conditional = 
SWITCH(
    TRUE(),
    ISBLANK(Sheet1[Scheduled Date]),"InProgress",
    Sheet1[Scheduled Date]="null","InProgress",
    Sheet1[Conditional]
)

In your example, you don't really need the "null" statement but I put it there just in case you encounter it in your data model. You can use as many conditions as you need (including && operators, for example for the Type B Products).

Best,

[MNedix · Accepted solution]

@Sidzx6r it would be great if you marked an answer as the solution to your problem so other users can see it.

Best,

[rajendraongole1 · Accepted solution]

Hi @Sidzx6r - Create a new column as below to check on scheduled date.

Conditional =

IF (

    ISBLANK(ScheduledT[Scheduled Date]),

    "NoProgress",

    IF (

        ScheduledT[Type] = "A" && ScheduledT[Product] <> "HOLIDAY",

        "InProgress",

        ""

    )

)

you can join the calendar and schedule tables based on date column 

Create merged table reflects the correct structure and that the conditional column shows "NoProgress" for dates without scheduled entries. 

Hope it works. 

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!