How to do a PROJECT in DAX
-
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 PatientIDFROM Patient pWHERE EXISTS (SELECT 1 FROM PatientSymptom WHERE [patientID] = p.[PatientID] AND ps.SymptomName="Heart Failure") -
Do you have a PowerBI or Power Pivot sample data / model?
-
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!
-
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...
-
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[/url].
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")))
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply