How to do a PROJECT in DAX

  • pietlinden

    SSC Guru

    Points: 62456

    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

    SSC Guru

    Points: 182356

    Do you have a PowerBI or Power Pivot sample data / model?

    😎

  • pietlinden

    SSC Guru

    Points: 62456

    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!

  • pietlinden

    SSC Guru

    Points: 62456

    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

    SSC Guru

    Points: 62456

    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 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply