Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin 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.
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)
Solved! Go to Solution.
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.
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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.
To learn more about Power BI, follow me on Twitter or subscribe 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.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
14 | |
13 | |
8 | |
8 | |
7 |
User | Count |
---|---|
17 | |
13 | |
7 | |
6 | |
6 |