Translating T-SQL INNER JOIN statement into DAX

Question

I have the following code in T-SQL, where it's calculated distinct [ClientID] of [C-Services] table, based on certain conditions, and with joining [C_Episode] table on a multiple fields:

SELECT 
   cs.[ProgramName] AS [Program]
  ,COUNT(DISTINCT cs.[ClientID]) AS [# Clients Served]

FROM 
  [C_Services] cs
  INNER JOIN [C_Episodes] epi ON (

        (epi.[ProgramID] = cs.[ProgramID])
    AND (epi.[ClientID] = cs.[ClientID])
    AND (epi.[AdmissionDate] <= cs.[ServiceMonth])
    AND (epi.[DischargeDate] >= cs.[ServiceMonth])
                                        )
WHERE
       (cs.[ClientID] > 0)
   AND (cs.[IsNoShow] = 0) 
   AND (cs.[IsCancellation] = 0)
   AND (cs.[IsNonClaimable] = 0)

GROUP BY
     cs.[ProgramName]

Also, I also have DAX Measure associated with the above code, but without INNER JOIN [C-Episodes] table:

VAR countClients =
    CALCULATE (
       DISTINCTCOUNT ( 'C_Services'[ClientID] ),
               FILTER (
                       'C_Services',
                              [ClientID] > 0
                           && [Is Non-Claimable] = FALSE ()
                           && [Is No-show] = FALSE ()
                           && [Is Cancellation] = FALSE ()
                      )
                 )
                 RETURN
                      countClients

My question is - How do I have to modify DAX measure in order to add INNER JOIN [C_Episodes] table (joined on three conditions stated in T-SQL code)?

I was trying to add, for the 1st JOIN ON ProgramID column

NATURALINNERJOIN('C_Services'[ProgramID], 'C_Episodes'[ProgramID])

But it given an error - "Grouping needed", and after I was trying to change my Calculate to Summarize or to Summarizecolumns - it still given an errors.

Answer 1

The SQL can be rewritten like this:

SELECT 
   cs.[ProgramName] AS [Program]
  ,COUNT(
       DISTINCT 
   CASE WHEN EXISTS (
       SELECT * 
       FROM [C_Episodes] epi
       WHERE (epi.[ProgramID] = cs.[ProgramID])
       AND (epi.[ClientID] = cs.[ClientID])
       AND (epi.[AdmissionDate] <= cs.[ServiceMonth])
       AND (epi.[DischargeDate] >= cs.[ServiceMonth])
  ) THEN cs.[ClientID] ELSE NULL END
) AS [# Clients Served]

FROM 
  [C_Services] cs
WHERE
       (cs.[ClientID] > 0)
   AND (cs.[IsNoShow] = 0) 
   AND (cs.[IsCancellation] = 0)
   AND (cs.[IsNonClaimable] = 0)

GROUP BY
     cs.[ProgramName]

and this is more in line with DAX's measure-centric view of the world.

If we

• focus on the [# Clients Served] measure
• assume that the ProgramName group by will be handled by the visualisation
• Assume your existing filter can be reused

Then you should be able to use this for a column calculation in the C_Episodes table:

ClientMatched = 
IF(
    ISEMPTY(
        FILTER(
        'C_Episodes', 
        'C_Episodes'[ProgramID] = 'C_Services'[ProgramID] &&
        'C_Episodes'[ClientID] = 'C_Services'[ClientID] &&
        'C_Episodes'[AdmissionDate] <= 'C_Services'[ServiceMonth] &&
        'C_Episodes'[DischargeDate] >= 'C_Services'[ServiceMonth]
        )
    ),
    BLANK(),
    [ClientID]
)

If there is a matching record in C_Episodes, the column will contain the ClientID, if not it will be blank.

This can be also extended with your other filter.

Now you can use this measure:

DistinctClientCount = DISTINCTCOUNTNOBLANK(Services[ClientMatched])

I'm not sure how efficient this is but try it out

For completeness, these are DAX expressions to create the tables I used to test:

C_Services

C_Services = UNION(
    ROW("ProgramId", 1, "ClientId", 1,"ServiceMonth",DATE(2021,2,1)),
    ROW("ProgramId", 1, "ClientId", 1,"ServiceMonth",DATE(2021,3,1)),
    ROW("ProgramId", 1, "ClientId", 1,"ServiceMonth",DATE(2021,4,1)),
    ROW("ProgramId", 1, "ClientId", 2,"ServiceMonth",DATE(2021,2,1)),
    ROW("ProgramId", 1, "ClientId", 2,"ServiceMonth",DATE(2021,3,1)),
    ROW("ProgramId", 1, "ClientId", 2,"ServiceMonth",DATE(2021,4,1)),
    ROW("ProgramId", 2, "ClientId", 1,"ServiceMonth",DATE(2021,2,1)),
    ROW("ProgramId", 2, "ClientId", 1,"ServiceMonth",DATE(2021,3,1)),
    ROW("ProgramId", 2, "ClientId", 1,"ServiceMonth",DATE(2021,4,1))
)

C_Episodes

C_Episodes = UNION(
    ROW("ProgramId", 1, "ClientId", 1,"AdmissionDate",DATE(2021,1,15),"DischargeDate",DATE(2021,2,15)),
    ROW("ProgramId", 1, "ClientId", 2,"AdmissionDate",DATE(2022,5,15),"DischargeDate",DATE(2022,6,15)),
    ROW("ProgramId", 4, "ClientId", 1,"AdmissionDate",DATE(2021,2,15),"DischargeDate",DATE(2021,3,15)),
    ROW("ProgramId", 1, "ClientId", 2,"AdmissionDate",DATE(2021,2,15),"DischargeDate",DATE(2021,3,15))    
)