How to add INNER JOIN but in DAX to an existing Measure?

[Hell-1931]

Hello,

I have the following measure:

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

which was translated from the following T-SQL:

SELECT 
	 cs.[ProgramName] AS [Program]
	,COUNT(DISTINCT cs.[ClientID]) 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]

But the INNER JOIN was added to SQL:

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]) )

                                            

How do I write this new " INNER JOIN " addition in DAX?

I was trying the following to join on the 1st column [ProgramID] :

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

 But it gives an error "grupping needed"; was trying to modify Calculate statement also, but no success...

Please, HELP!

[Anonymous · Accepted solution]

Hi @Hell-1931 ,

First, you need to create relationships between C_Services and C_Episodes tables base on the field ProgramID and ClientID just as shown in below screenshot.

Then create a measure as below:

Measure = 
VAR _addate =
    CALCULATE (
        MAX ( 'C_Episodes'[AdmissionDate] ),
        USERELATIONSHIP ( 'C_Episodes'[ClientID], 'C_Services'[ClientID] )
    )
VAR _dsdate =
    CALCULATE (
        MAX ( 'C_Episodes'[DischargeDate] ),
        USERELATIONSHIP ( 'C_Episodes'[ClientID], 'C_Services'[ClientID] )
    )
VAR countClients =
    CALCULATE (
        DISTINCTCOUNT ( 'C_Services'[ClientID] ),
        USERELATIONSHIP ( 'C_Episodes'[ClientID], 'C_Services'[ClientID] ),
        FILTER (
            'C_Services',
            'C_Services'[ClientID] > 0
                && 'C_Services'[Is Non-Claimable] = 0
                && 'C_Services'[Is No-show] = 0
                && 'C_Services'[Is Cancellation] = 0
                && 'C_Services'[ServiceMonth] >= _addate
                && 'C_Services'[ServiceMonth] <= _dsdate
        )
    )
RETURN
    countClients

Best Regards

[CNENFRNL]

I don't think it's necessary to deploy INNER JOIN to DAX on purpose as filtering along with 1:* relationship is an instrinsic behavior in DAX. That's to say, once a relationship is created between C_Services[ProgramID] and C_Episodes[ProgramID], it constitute naturally an inner join.