Switching from DAX to M for nested IFs and TODAY condition

[Anonymous]

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

[Greg_Deckler · Accepted solution]

DateTime.LocalNow and DateTime.FixedLocalNow

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

@ImkeF probably knows about the switch statement.

[artemus · Accepted solution]

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"

[Anonymous]

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

[artemus]

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

[ImkeF]

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.

[artemus]

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.

[ImkeF]

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?

[artemus]

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.

[Greg_Deckler · Accepted solution]

DateTime.LocalNow and DateTime.FixedLocalNow

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

@ImkeF probably knows about the switch statement.