cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Agherardi Resolver I
Resolver I

Switching from DAX to M for nested IFs and TODAY condition

Hi

I have the following DAX statement that creates a calculated column:

Paid =
SWITCH(
TRUE(),
'Dataset'[status] = "Open" && 'Dataset'[Due Date] < TODAY(), "Not Paid",
'Dataset'[status] = "Open" && 'Dataset'[Due Date] >= TODAY(), "To be Paid",
"Paid"
)

I would like to move this calculation to M. I have two questions:

1- What is the function in M that returns the current day?
2-Is there a way to perform multiple conditions with a switch function in M? Or do I have to write every if-else for each condition?

Thanks
2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User IV
Super User IV

Re: Switching from DAX to M for nested IFs and TODAY condition

DateTime.LocalNow and DateTime.FixedLocalNow

 

https://docs.microsoft.com/en-us/powerquery-m/datetime-localnow

 

@ImkeF probably knows about the switch statement.


---------------------------------------

Not the Power BI thought police...

I have NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

View solution in original post

Microsoft artemus
Microsoft

Re: Switching from DAX to M for nested IFs and TODAY condition

While you can kind of do switch in M, for what you have if/else is good enough.

E.g.

= if [status] = "Open" and [Due Date] < DateTime.FixedLocalNow() then
     "Not Paid"
   else if [status] = "Open" and [Due Date] >= DateTime.FixedLocalNow() then
     "To be Paid"
   else
     "Paid"

View solution in original post

8 REPLIES 8
Super User IV
Super User IV

Re: Switching from DAX to M for nested IFs and TODAY condition

DateTime.LocalNow and DateTime.FixedLocalNow

 

https://docs.microsoft.com/en-us/powerquery-m/datetime-localnow

 

@ImkeF probably knows about the switch statement.


---------------------------------------

Not the Power BI thought police...

I have NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

View solution in original post

Microsoft artemus
Microsoft

Re: Switching from DAX to M for nested IFs and TODAY condition

While you can kind of do switch in M, for what you have if/else is good enough.

E.g.

= if [status] = "Open" and [Due Date] < DateTime.FixedLocalNow() then
     "Not Paid"
   else if [status] = "Open" and [Due Date] >= DateTime.FixedLocalNow() then
     "To be Paid"
   else
     "Paid"

View solution in original post

Agherardi Resolver I
Resolver I

Re: Switching from DAX to M for nested IFs and TODAY condition

I already did it like that, but I would be interested in knowing how to do a kind of switch function in M anyway. 

Microsoft artemus
Microsoft

Re: Switching from DAX to M for nested IFs and TODAY condition

Switch in M:

let 
   cases = 
   [
      MyTerm1 = "My term one",
      MyTerm2 = "My term two",
      MyTerm3 = if [someOtherColumn] > 0 then "Yes" else "No"
   ]
in
   try Record.Field(cases, [MyColumnValue]) otherwise "My default case"

There is also a similar one for table syntax. Also note that perf is bad if default case is hit a lot

Super User III
Super User III

Re: Switching from DAX to M for nested IFs and TODAY condition

Hi @artemus 

would very much like to understand why performance only drops if the default-value is triggered.

I was doing some performance test with record lookups and found them very fast, as they evaluated lazily. But used them without the try ... otherwise - extension.

Just curious to understand why using try...otherwise wouldn't impact performance per se, but the occurrance of the "otherwise" will cause the drop.

Or did I understand this wrong?

Thanks heaps.

 

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark my post as a solution. Thanks!

Imke Feldmann

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

 
Microsoft artemus
Microsoft

Re: Switching from DAX to M for nested IFs and TODAY condition

Try .. otherwise uses exception handling nativly (at least it appears this way). Since an exception can handle several levels deep, going back up the stack isn't performant.

 

Thought about this a bit more, this perf issue can be avoided by using Record.FieldOrDefault function.

Super User III
Super User III

Re: Switching from DAX to M for nested IFs and TODAY condition

Thanks @artemus for the alternative solution.

Not sure how to interpret the "levels" in your explanation: We're dealing with a record here that (at least for my tests) evaluates lazily: Just evaluates the field that matches the condition/lookup. Where are there any levels? Doesn't it return just the one field from the record?

 

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark my post as a solution. Thanks!

Imke Feldmann

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

 
Highlighted
Microsoft artemus
Microsoft

Re: Switching from DAX to M for nested IFs and TODAY condition

try .. otherwise supports multiple levels.

E.g.

try
   let
      factorialSkip = (n) => if n > 0 then n * @factorialSkip(n - 2) else if n = 1 then error "Odd numbers not allowed" else 1
   in
      factorialSkip(9)

In this case, factorialSkip is called with 9, then 7, then 5, then 3, then 1, which results in an error.

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors