SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to do a PROJECT in DAX


How to do a PROJECT in DAX

Author
Message
pietlinden
pietlinden
SSC-Dedicated
SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)

Group: General Forum Members
Points: 34051 Visits: 15491
I have a table of Patient Symptoms and a table of Patients in my model. What I want to do in DAX is an INTERSECT like you can do in SQL.

CREATE TABLE Patient(PatientID INT IDENTITY PRIMARY KEY);
CREATE TABLE Symptom(SymptomName VARCHAR(20) PRIMARY KEY);
CREATE TABLE PatientSymptom(PatientID INT, SymptomName VARCHAR(20), ConsentDate DATE, Severity BYTE)

SELECT PatientID
FROM Patient p
WHERE EXISTS (SELECT 1 FROM PatientSymptoms ps WHERE ps.PatientID = p.PatientID AND ps.SymptomName='Diabetes' )
AND EXISTS (SELECT 1 FROM PatientSymptoms ps WHERE ps.PatientID = p.PatientID AND ps.SymptomName='Heart Failure');

How would i write the equivalent DAX query, so that I can see
1. Which patients had only 1 symptom, 2 symptoms, 3 symptoms.
I can filter just fine.

FILTER(PatientSymptom, [SymptomName]="Diabetes")



I thought I could do two filters and do an INTERSECT, like this:
INTERSECT(
FILTER(PatientSymptom, [SymptomName]="Diabetes"),
FILTER(PatientSymptom, [SymptomName]="Heart Failure")
)



but it looks like I need to project only the PatientID in the filter... the DAX version of this:

SELECT PatientID
FROM Patient p
WHERE EXISTS (SELECT 1 FROM PatientSymptom WHERE [patientID] = p.[PatientID] AND ps.SymptomName="Heart Failure")


Eirikur Eiriksson
Eirikur Eiriksson
SSC Guru
SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)

Group: General Forum Members
Points: 100302 Visits: 20906
Do you have a PowerBI or Power Pivot sample data / model?
Cool
pietlinden
pietlinden
SSC-Dedicated
SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)

Group: General Forum Members
Points: 34051 Visits: 15491
I have a really simple one in PowerBI with only a handful of Patients. It's really small on purpose, because I only need a few records to prove the query is right.

The basic structure is

Patient--(1,M)--PatientSymptom---(M,1)--SymptomList

The file should be attached. <G>
The problem I'm having (because I'm not very good at DAX) is that I want to project only the PatientID so that I can use set operations like INTERSECT and MINUS (well, the DAX version...)

Thanks!
Attachments
intersect woes.zip (16 views, 47.00 KB)
pietlinden
pietlinden
SSC-Dedicated
SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)

Group: General Forum Members
Points: 34051 Visits: 15491
I might be getting somewhere...

Since I only have 3 possible symptoms, and the total combinations is only 3!=3x2=6, I can just create a cheat matrix:
If I put this in the Patient table:
HasDiabetes:=COUNTROWS(FILTER(RELATEDTABLE('PatientSymptoms'),'PatientSymptoms'[Symptom]="Diabetes"))



and create the same calculated column for each of the symptoms, I can then find all the patients that have one and not the other pretty easily...
D_plus_CAD =([HasDiabetes]=1) && ([HasCAD]=1)



still not sure how I would do this if I wanted a matrix of symptoms and the count of overlaps ([Symptom] on both rows and columns and then COUNTROWS(RELATEDTABLE('Patient')) in the intersection...
pietlinden
pietlinden
SSC-Dedicated
SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)

Group: General Forum Members
Points: 34051 Visits: 15491
Okay, looks like I got bit by the "Reading Is Fundamental" bug...

The short answer is CALCULATETABLE.

When I actually OPENED Ferrari & Russo's DAX Patterns book, there's a chapter on Surveys (Chapter 13)... the question in the book is "Which customers gave the same answers for both Question1 and Question2?" and the answer is:

CALCULATE (
COUNTROWS ( Customers ),
CALCULATETABLE (
Answers,
USERELATIONSHIP (Answers[AnswerKey], Filter2[AnswerKey] )
),
CALCULATETABLE (
Answers,
USERELATIONSHIP (Anwers[AnswerKey], Filter1[AnswerKey] )
)
)


In case someone is looking for this, it's here.

Yep, "Here's your sign."

... after more playing around with this... I got this working... I guess CALCULATETABLE is something I need to figure out...

-- has HF
CALCULATETABLE('Patient',FILTER('Diagnoses',[SymptomName]="Heart Failure"))


-- has D
CALCULATETABLE('Patient',FILTER('Diagnoses',[SymptomName]="Diabetes"))


-- has both:
INTERSECT('has D', 'has HF')


or

INTERSECT (
CALCULATETABLE('Patient',FILTER('Diagnoses',[SymptomName]="Diabetes")),
CALCULATETABLE('Patient',FILTER('Diagnoses',[SymptomName]="Heart Failure"))
)

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search