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

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

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
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

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

June 2025 community update carousel

Fabric Community Update - June 2025

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