The Output based on multiple condition and Status column

[NickzNickz]

Hi ... 

I have table1 with Status , Date1 , Date2 , Date3 , Date4 and column Output.
The output is based on the following conditions:
1) If Status is Planning and Date1 exists, Date2 is empty, Date3 is empty, date4 is empty, the output is Planning
2) If Status is On going and Date1 exists, Date2 exists, Date3 is empty, date4 is empty, the output is Advertisement
3) If Status is On going and Date1 exists, Date2 exists, Date3 exists, date4 is empty, the output is Selling
4) If Status is Closed and Date1 exists, Date2 exists, Date3 exists, date4 exists, the output is Closing

The result will be used New Column and not as a measure.

How can I achieve that ...

Regards,

NickzNickz

[FreemanZ · Accepted solution]

hi @NickzNickz ,

try to add a calculated column like:

Flag = 
SWITCH(
    TRUE(),
    [date1]<>BLANK() && [date2]=BLANK(), "Planning",
    [date2]<>BLANK() && [date3]=BLANK(), "Advertisement",
    [date3]<>BLANK() && [date4]=BLANK(), "Selling",
    "Closing"
)

it worked like:

[NickzNickz]

Hi @FreemanZ 

I've tested the formula and it works.
Btw, I also added additional criteria because the original data has values I need to reference to make the output more accurate. 

Calendar Event 3 = 

VAR EventStatus = f_event_list[Status]
VAR Planning = f_event_list[Planning Date]
VAR Advertisement  = f_event_list[Advertisment Date]
VAR Selling = f_event_list[Date Selling Start]
VAR Closing = f_event_list[Original Closing Date]

RETURN
SWITCH(
    TRUE(),
    EventStatus = "Planning" && Planning<>BLANK() && Advertisement=BLANK(), "Planning",
    EventStatus = "On going" && Advertisement<>BLANK() && Selling=BLANK(), "Advertisement",
    EventStatus = "On going" && Selling<>BLANK() && Closing=BLANK(), "Selling",
    EventStatus = "Closed" && Closing<>BLANK(), "Closing",
    ""
)

 
Thank you.

Regards,

NickzNickz

[BeaBF]

@NickzNickz Hi! Try with:

NewOutput =
IF (
Table1[Status] = "Planning"
&& NOT ISBLANK ( Table1[Date1] )
&& ISBLANK ( Table1[Date2] )
&& ISBLANK ( Table1[Date3] )
&& ISBLANK ( Table1[Date4] ),
"Planning",
IF (
Table1[Status] = "On going"
&& NOT ISBLANK ( Table1[Date1] )
&& NOT ISBLANK ( Table1[Date2] )
&& ISBLANK ( Table1[Date3] )
&& ISBLANK ( Table1[Date4] ),
"Advertisement",
IF (
Table1[Status] = "On going"
&& NOT ISBLANK ( Table1[Date1] )
&& NOT ISBLANK ( Table1[Date2] )
&& NOT ISBLANK ( Table1[Date3] )
&& ISBLANK ( Table1[Date4] ),
"Selling",
IF (
Table1[Status] = "Closed"
&& NOT ISBLANK ( Table1[Date1] )
&& NOT ISBLANK ( Table1[Date2] )
&& NOT ISBLANK ( Table1[Date3] )
&& NOT ISBLANK ( Table1[Date4] ),
"Closing",
BLANK ()
)
)
)
)

BBF

[NickzNickz]

Hi @BeaBF ,

I have tested and the result not matching.

I also tested below formula :

Output Column = 
IF(
    f_event_list[Status] = "Planning" && NOT(ISBLANK(f_event_list[Planning Date])), "Planning",
    IF(
        f_event_list[Status] = "On going" && NOT(ISBLANK(f_event_list[Planning Date])) && NOT(ISBLANK(f_event_list[Advertisment Date])) && (ISBLANK(f_event_list[Date Selling Start])), "Advertisement",
        IF(
            f_event_list[Status] = "On going" && NOT(ISBLANK(f_event_list[Planning Date])) && NOT(ISBLANK(f_event_list[Advertisment Date])) && NOT(ISBLANK(f_event_list[Date Selling Start])), "Selling",
             IF(
                f_event_list[Status] = "Closed" && NOT(ISBLANK(f_event_list[Planning Date])) && NOT(ISBLANK(f_event_list[Advertisment Date])) && NOT(ISBLANK(f_event_list[Original Closing Date])) && NOT(ISBLANK(f_event_list[Original Closing Date])), "Closing",
                BLANK()
            )
        )
    )
)

and the result as below. I still have an issue with On going status .... 

Regards,

NickzNickz