Forum Discussion
Dax to M language
Hi everyone!
I wanted help transforming the following DAX code to M language:
Classification =
var __currentAccountNumber = 'Accounts Receivable'[Account Number]
var __currentDebt = 'Accounts Receivable'[Debt USD]
var __currentInvoiceType = 'Accounts Receivable'[Invoice Type]
var __currentTimeInterval = 'Accounts Receivable'[Time Interval]
var __sumOfDebtForCurrentClient =
SUMX(
FILTER(
'Accounts Receivable',
'Accounts Receivable'[Account Number] = __currentAccountNumber
),
'Accounts Receivable'[Debt USD]
)
var __classification =
SWITCH( TRUE(),
__sumOfDebtForCurrentClient < 0,
"Credit Balance",
__currentDebt < 0 && __currentInvoiceType in {"AA", "BB", "CC", "DD"},
"Unapplied Payment",
__currentTimeInterval
)
return
__classificationI initially tried to add this column but turns out I need it to be added as steps in the Query Editor so I can use the result to merge another table.
Thanks!
In power query, you can use Table.SelectRows(Source, each([Column] = Value)) to replace FILTER() in dax, use List.Sum() to replace SUMX() or SUM() indax, but there's no switch key word and syntax in power query, so you need to write a custom function, please refer to blogs below:
https://www.powerquery.training/portfolio/replicate-power-pivots-switch-function/
Community Support Team _ Jimmy Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
1 Reply
- v-yuta-msft
Community Support
In power query, you can use Table.SelectRows(Source, each([Column] = Value)) to replace FILTER() in dax, use List.Sum() to replace SUMX() or SUM() indax, but there's no switch key word and syntax in power query, so you need to write a custom function, please refer to blogs below:
https://www.powerquery.training/portfolio/replicate-power-pivots-switch-function/
Community Support Team _ Jimmy Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.