Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.