Forum Discussion
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
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
DateTime.LocalNow and DateTime.FixedLocalNow
https://docs.microsoft.com/en-us/powerquery-m/datetime-localnow
ImkeF probably knows about the switch statement.
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"
8 Replies
- Greg_Deckler
Community Champion
DateTime.LocalNow and DateTime.FixedLocalNow
https://docs.microsoft.com/en-us/powerquery-m/datetime-localnow
ImkeF probably knows about the switch statement.
artemusMicrosoft Employee
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"- 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