M Code for a DAX expression

[Simon_Evans]

Hi Everyone

I need to convert or know how to write the following DAX Add Column in M

Exclude = IF(SessionAttendanceLeavers[DateOfLeaving] < LOOKUPVALUE(AcademicYears[Start Date],AcademicYears[Term],"Autumn"),"Remove","Keep")

The above works totally fine but I need to be able to add this column as an Applied Step in Transform Data so that I can apply a filter as a following step to remove certain rows from the table. 

SessionAttendanceLeavers and AcademicYears are 2 un-related tables.

Does anyone know how I can create this in M Code please?

[vojtechsima]

Hi, @Simon_Evans 
Please share sample data for each table so I can play with the data and give you results. Thanks

Something like this for each table:
IdDateUserId

1	01.01.2022	2
2	27.01.2022	1
3	02.02.2022	2
4	20.03.2022	7

[Simon_Evans]

Hi @vojtechsima 

Apologies for the delay. Please see the example I have put together below.

SessionAttendanceLeavers table example

AttendanceCode	StudentID	DateofLeaving	Employee
A1329183376	A92214024	23/07/2021	null
A1161584171	A92214024	23/07/2021	null
A1161584171	A352963710	10/10/2021	null
A930902628	A352963710	10/10/2021	null
A1329183376	A1840101401	1/1/2022	null

AcademicYears table

Type	Start Date	End Date
Term	01/09/2021	17/12/2021
Term	04/01/2022	01/04/2022
Term	19/04/2022	22/07/2022

[vojtechsima · Accepted solution]

Hi, @Simon_Evans 
Thanks for the data, it always helps so much:
Add this as a new Custom Column:

let
dateofleaving = [DateofLeaving],
matchingTerm = Table.SelectRows(AcademicYears, each dateofleaving > _[Start Date] and _[Season] = "Autumn")
in
if not(Table.IsEmpty(matchingTerm)) then "Keep" else "Remove"

Please note, I had to adjust your custom data and I had to add type of Season so I was able to retrieve the needed information.
I attached the file, I worked with.

[Simon_Evans]

Thank you so much, that is perfect!