topic Re: The Output based on multiple condition and Status column in DAX Commands and Tips

The Output based on multiple condition and Status column

NickzNickz — Thu, 02 Nov 2023 09:36:45 GMT

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

Re: The Output based on multiple condition and Status column

BeaBF — Thu, 02 Nov 2023 09:51:48 GMT

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

Re: The Output based on multiple condition and Status column

NickzNickz — Fri, 03 Nov 2023 01:03:36 GMT

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

Re: The Output based on multiple condition and Status column

FreemanZ — Fri, 03 Nov 2023 02:03:17 GMT

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:

Re: The Output based on multiple condition and Status column

NickzNickz — Fri, 03 Nov 2023 03:54:04 GMT

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