DAX script to M language

[jfontoura]

Hi.

I would like to know if this calculated column in DAX is possible to be achieved in Advanced Editor (M language):

calculatedColumn = CALCULATE (

VALUES ( 'AuxTable'[value] ),

FILTER (

'AuxTable',

'AuxTable'[start_date] <= EARLIER ( 'MyTable'[date] )

&& 'AuxTable'[end_date] >= EARLIER ( 'MyTable'[date] )

)

)

(Basically I need to get the value from AuxTable[value] in the row where MyTable[date] is in between AuxTable[start_date] and AuxTable[end_date])

Thanks in advance.

[AlexisOlson · Accepted solution]

I think the custom column would look something like this:

Table.SelectRows(AuxTable, (row) => row[start_date] <= [date] and row[end_date] >= [date])[value]{0}

In the above, row is just a variable name. I could have used x or r or _ or whatever.

[jfontoura]

Perfect. Thank you very much.

[AlexisOlson]

Is there always a unique value satisfying those conditions? VALUES will return a list rather than a single value if not.

[jfontoura]

Yes, guaranteed always a single value per satisfied conditions.

[selimovd]

Hey @jfontoura ,

from a first look you should be able to do that in M.

There you might have to do a join (Merge queries).

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi