topic Re: DAX - If "Date1" is blank, then "Date2", if both are blank, "Date 3" in DAX Commands and Tips

DAX - If "Date1" is blank, then "Date2", if both are blank, "Date 3"

jmetf150 — Fri, 29 Dec 2023 23:40:18 GMT

Hello - I am looking for assistance to help me learn writing DAX formulas concerning dates and blanks. This would be very similar to an "IF" statement but trying to understand if IF or SWITCH would be better for what I need.

Per the below table, I am trying to write a formula that will tell me this:

Show me Org date unless there is a Res date, then show me the Res date. If both are blank, show me the Del date.

Org	Res	Del	New (Outcome)
5/26/90	6/28/90	6/15/91	6/28/90
3/5/91		4/25/91	3/5/91
		6/15/91	6/15/91

Re: DAX - If "Date1" is blank, then "Date2", if both are blank, "Date 3"

gmsamborn — Sat, 30 Dec 2023 04:11:52 GMT

Hi jmetf150

Would something like this help?

NewDate = COALESCE( [Res], COALESCE( [Org], [Del] ) )

It should be able to be used as a calculated column or as a measure.

Re: DAX - If "Date1" is blank, then "Date2", if both are blank, "Date 3"

Dangar332 — Sat, 30 Dec 2023 06:30:22 GMT

Hi, jmetf150

it depend on your conditions

if you have one or two condition then use IF() function .you can use if when more then two condition but it little bit hard .

if you have more than two condition then easy to use switch() function. but in switch sequence matter if sequence is not in correct order then it give unexpected result

With IF() function

column = if( tablename[res]<>blank(),tablename[res], if( tablename[org]<>blank(),tablename[res],tablename[del] ) )

With switch() function

column= switch( true(), tablename[res]<>blank(),tablename[res], tablename[org]<>blank(),tablename[org], tablename[del] )

Re: DAX - If "Date1" is blank, then "Date2", if both are blank, "Date 3"

ThxAlot — Sat, 30 Dec 2023 14:55:18 GMT

= COALESCE( [Res], [Org], [Del] )

Re: DAX - If "Date1" is blank, then "Date2", if both are blank, "Date 3"

jmetf150 — Sat, 30 Dec 2023 15:59:39 GMT

Thanks Alot ThxAlot! 🙂 I tried your proposed solution first because it was very simple to write/understand (at least for a newbie anyway). I am going to read more about the COALESCE function to better understand its purpose and when to use it.

Re: DAX - If "Date1" is blank, then "Date2", if both are blank, "Date 3"

jmetf150 — Sat, 30 Dec 2023 16:05:05 GMT

Thank you for your solution. Both the IF and SWITCH worked also.

Re: DAX - If "Date1" is blank, then "Date2", if both are blank, "Date 3"

jmetf150 — Sat, 30 Dec 2023 16:06:38 GMT

What is the difference between a "Calculated Column" and a "Measure"? Why would you use one over the other? What are the benefits of using either?

Re: DAX - If "Date1" is blank, then "Date2", if both are blank, "Date 3"

gmsamborn — Sat, 30 Dec 2023 16:52:17 GMT

Hi jmetf150

One example of using a calculated column is if all 3 dates are columns from the same table. If the result is not meant to be dynamic, a calculated column would work since they are only evaluated once at refresh time.

One example of using a measure would be if the 3 dates are measures. The tables in the underlying measures could be different tables. By building the logic into the new measure, you could handle a dynamic scenario in which the result would be affected by slicers, filters, etc.

I hope this makes sense.

(Thumbs up to ThxAlot because until now I thought COALESCE() only had 2 parameters.)

Re: DAX - If "Date1" is blank, then "Date2", if both are blank, "Date 3"

gmsamborn — Sat, 30 Dec 2023 17:39:44 GMT

Hi jmetf150

After re-reading your question, I realized my answer was specific to this thread.

If you need a more general description, this should get you started.

https://dash-intel.com/powerbi/modeling_measure+calculated+columns.php#:~:text=A%20measure%20is%20sometimes%20incorrectly,be%20used%20as%20a%20filter.