Forum Discussion

NickzNickz's avatar
NickzNickz
Icon for Helper IV rankHelper IV
2 years ago
Solved

The Output based on multiple condition and Status column

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

"Need Help"
  • FreemanZ's avatar
    FreemanZ
    2 years ago

    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:

4 Replies

  • BeaBF's avatar
    BeaBF
    Icon for Super User rankSuper User
    2 years ago

    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's avatar
      NickzNickz
      Icon for Helper IV rankHelper IV
      2 years ago

      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

  • FreemanZ's avatar
    FreemanZ
    Icon for Super User rankSuper User
    2 years ago

    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's avatar
      NickzNickz
      Icon for Helper IV rankHelper IV
      2 years ago

      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