Convert DAX to M Code - Compare Records from Two Tables

Hi jguercio,

There are a number of ways to deal with this.

Would have been easier to answer definitively with some sample data but check out the below code and attached PBIX for an example.

Basically, I merged based on id and service and then added the conditional column when between the two dates.

authStatus

let
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText(
          "i45WMlTSUTLUN9Q3MjAyAjITlWJ1opVALCMDfSOYaBJY1Bik1ELfGCaaDBY1gaiFm5CiFBsLAA==",
          BinaryEncoding.Base64
        ),
        Compression.Deflate
      )
    ),
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [id = _t, contactDate = _t, service = _t]
  ),
  #"Changed Type" = Table.TransformColumnTypes(
    Source,
    {{"id", Int64.Type}, {"contactDate", type date}, {"service", type text}}
  ),
  #"Merged Queries" = Table.NestedJoin(
    #"Changed Type",
    {"id", "service"},
    authStatus,
    {"id", "procedureType"},
    "authStatus",
    JoinKind.LeftOuter
  ),
  #"Expanded authStatus" = Table.ExpandTableColumn(
    #"Merged Queries",
    "authStatus",
    {"authSD", "authED"},
    {"authSD", "authED"}
  ),
  #"Added Custom" = Table.AddColumn(
    #"Expanded authStatus",
    "Custom",
    each try if [contactDate] >= [authSD] and [contactDate] <= [authED] then 1 else 0 otherwise 0
  )
in
  #"Added Custom"

service

let
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText(
          "i45WMlTSUTLUN9Q3MjAyAjITlWJ1opVALCMDfSOYaBJY1Bik1ELfGCaaDBY1gaiFm5CiFBsLAA==",
          BinaryEncoding.Base64
        ),
        Compression.Deflate
      )
    ),
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [id = _t, contactDate = _t, service = _t]
  ),
  #"Changed Type" = Table.TransformColumnTypes(
    Source,
    {{"id", Int64.Type}, {"contactDate", type date}, {"service", type text}}
  ),
  #"Merged Queries" = Table.NestedJoin(
    #"Changed Type",
    {"id", "service"},
    authStatus,
    {"id", "procedureType"},
    "authStatus",
    JoinKind.LeftOuter
  ),
  #"Expanded authStatus" = Table.ExpandTableColumn(
    #"Merged Queries",
    "authStatus",
    {"authSD", "authED"},
    {"authSD", "authED"}
  ),
  #"Added Custom" = Table.AddColumn(
    #"Expanded authStatus",
    "Custom",
    each try if [contactDate] >= [authSD] and [contactDate] <= [authED] then 1 else 0 otherwise 0
  )
in
  #"Added Custom"

I hope this helps. If not, please provide some sample data.