Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
jfontoura
New Member

DAX script to M language

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.
1 ACCEPTED SOLUTION
AlexisOlson
Super User
Super User

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.

 

View solution in original post

5 REPLIES 5
AlexisOlson
Super User
Super User

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.

 

Perfect. Thank you very much.

AlexisOlson
Super User
Super User

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

Yes, guaranteed always a single value per satisfied conditions.

selimovd
Super User
Super User

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
 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.