How to write this measure from a corresponding SQL

[Bokazoit]

I have this SQL:

WITH

	Arytmi AS (
		SELECT 
			ResponderId,
			AnswerId = SvarHjertelinjenKey
		FROM [dm].[FactHjertelinjen]
		where
			Kilde = 'Hjertelinjen Forms'
			AND SvarHjertelinjenKey = 1000010
			),

	Guide AS (
		SELECT 
			ResponderId,
			AnswerId = SvarHjertelinjenKey
		FROM [dm].[FactHjertelinjen]
		where
			Kilde = 'Hjertelinjen Forms'
			AND SvarHjertelinjenKey IN (1000048,1000050)
			)

	SELECT
		a.ResponderId,
		HeartFailure = a.AnswerId,
		DirectedToOwnDoctor = MAX(CASE WHEN g.AnswerId IN (1000048) THEN g.AnswerId ELSE NULL END),
		DirectedToMedicalCenter = MAX(CASE WHEN g.AnswerId IN (1000050) THEN g.AnswerId ELSE NULL END)
	FROM Arytmi a
	JOIN Guide g ON a.ResponderId = g.ResponderId
	GROUP BY
		a.ResponderId,
		a.AnswerId
	order by
		1

The SQL finds in the first CTE the ResponderId who has a certain HeartFailure. The next CTE looks for ResponderId's That has been sent to either own doctor or Medical center. I then compare to get a list a responderId's that has the specific heartfailure and been sent to both or either places - The reason for the Max in SQL

What would the measure in DAX look like?

I guess it can be done but I can't figure it out 🙂

[Bokazoit]

Output expected:

ResponderId	HeartFailure	DirectedToOwnDoctor	DirectedToMedicalCenter
1000097	1000010	NULL	1000050
1000103	1000010	NULL	1000050
1000115	1000010	1000048	NULL
1000117	1000010	1000048	NULL
1000118	1000010	1000048	NULL
1000121	1000010	1000048	NULL
1000134	1000010	1000048	NULL
1000144	1000010	1000048	1000050
1000156	1000010	1000048	NULL
1000158	1000010	NULL	1000050
1000161	1000010	NULL	1000050
1000166	1000010	1000048	NULL
1000167	1000010	1000048	1000050
1000179	1000010	1000048	NULL
1000189	1000010	1000048	NULL
1000193	1000010	1000048	NULL
1000194	1000010	1000048	NULL

[mh2587]

Can you explain the Expected Output?

[Bokazoit]

The output gives me one responderId for the Heartfailure which is only one answer - Arytmi in the CTE

In the Guide CTE I look for ResponderId for to Answers - DirectedToOwnDoctor or DirectedToMedicalCenter

Finally I Join the two to get only one ResponderId for the Arytmi and one line with either DirectedToOwnDoctor or DirectedToMedicalCenter or both.

The result is used to show how many heartfailures are send to DirectedToOwnDoctor or DirectedToMedicalCenter and by that I can or hope to create a Count on ResponderId.

Does that make sense?

[mh2587]

Try with Count(table[ResponderId])

[Bokazoit]

That would have been easy and I could have figured that out myself. It is a questionary where the respondent answers several questions while we are talking to he/she. One question is the Heartfailure type with an answerId. The other question is about the guidians to doctor etc. and comes from the same questionary but I need the cross reference between the two answers and the corresponding ResponderId.

[mh2587]

If you provide logic or concept behind this query or dummy expected output then may be we can achieve this