Convert DAX to M Code - Compare Records from Two Tables

[jguercio]

Hi,

I need to move this calculated column upstream in the process to Power Query. It should be simple enough, but I am struggling. The basic idea behind the calculated column is to evaluate whether a type of service occurred during an authorization span of that type of service. The DAX works, but I need it in M.

Optum Approved = 
VAR OptumTable =
    FILTER(
        'FACT-Optum Auth Status',
        'FACT-Service'[Optum ID] = 'FACT-Optum Auth Status'[Optum ID]
            && 'FACT-Service'[Contact Date] >= 'FACT-Optum Auth Status'[Optum Auth Start Date]
            && 'FACT-Service'[Contact Date] <= 'FACT-Optum Auth Status'[Optum Auth End Date]
            && 'FACT-Optum Auth Status'[Procedure Type] = 'FACT-Service'[Service]
    )
RETURN
    IF( COUNTROWS( OptumTable ) >=1,1,0)

[KNP · Accepted solution]

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.