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

[jmetf150]

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

[Dangar332 · Accepted solution]

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]
)

[ThxAlot · Accepted solution]

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

[ThxAlot · Accepted solution]

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

[jmetf150 · Accepted solution]

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.

[Dangar332 · Accepted solution]

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]
)

[jmetf150]

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

[gmsamborn]

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.

[jmetf150]

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?

[gmsamborn]

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%20so....

[gmsamborn]

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.)