Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
NZCraig
Helper I
Helper I

If statement with multiple nulls

Hi all,

 

I am new to M language and have gone down a rabbit hole trying to create a column to return a single date from many possiblities based on a heirarchy.

 

The plain language version of what I am trying to acheive is:

 

If the [assignment.scheduledate] field is not empty, and the [assignment.scheduledate] is greater than or equal to the [schedstart] field, then retun the [assignment.scheduledate].

 If the [assignment.scheduledate] field is not empty, and the [assignment.scheduledate] is less than the [schedstart] field, then retun the [schedstart] field.

If the [assignment.scheduledate] field is empty, and the [schedstart] field is not, return the [schedstart] field.

If the [assignment.scheduledate]  field and [schedstart] fields are both empty, return the [targstartdate] field.

 

I've tried various versions of the statement below but had no luck. Any help would be appreciated!

 

Thanks.

 

 

if [assignment.scheduledate] <> null and ([assignment.scheduledate] => [shedstart]) then [assignment.scheduledate] else
if [assignment.scheduledate] <> null and ([assignment.scheduledate] < [shedstart]) then [schedstart] else
if [assignment.scheduledate] is null and [schedstart] <> null then [schedstart] else
if [assignment.scheduledate] is null and [schedstart] is null then [targstartdate] else null)

 

1 ACCEPTED SOLUTION
mahoneypat
Microsoft Employee
Microsoft Employee

Your statement was very close!  Two typos - 1. [schedstart] was spelled [shedstart] in two places and 2. you typed "=>" instead of ">=", so M thought you were starting to type a function.  Here is the code that works:

 

if [assignment.scheduledate] <> null and ([assignment.scheduledate] >= [schedstart]) then [assignment.scheduledate] else
if [assignment.scheduledate] <> null and ([assignment.scheduledate] < [schedstart]) then [schedstart] else
if [assignment.scheduledate] is null and [schedstart] <> null then [schedstart] else
if [assignment.scheduledate] is null and [schedstart] is null then [targstartdate] else null

 

If it works, please mark this as the solution.  Please let me know if any questions.

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

2 REPLIES 2
mahoneypat
Microsoft Employee
Microsoft Employee

Your statement was very close!  Two typos - 1. [schedstart] was spelled [shedstart] in two places and 2. you typed "=>" instead of ">=", so M thought you were starting to type a function.  Here is the code that works:

 

if [assignment.scheduledate] <> null and ([assignment.scheduledate] >= [schedstart]) then [assignment.scheduledate] else
if [assignment.scheduledate] <> null and ([assignment.scheduledate] < [schedstart]) then [schedstart] else
if [assignment.scheduledate] is null and [schedstart] <> null then [schedstart] else
if [assignment.scheduledate] is null and [schedstart] is null then [targstartdate] else null

 

If it works, please mark this as the solution.  Please let me know if any questions.

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Thanks so much, I had gotten so far down the rabbit hole trying to work out nested statements, I missed the most obvious mistakes!

 

Really appreciated.

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.