Counts based on two groups in DAX

• pietlinden

  SSC Guru

  Points: 63428

  More actions

  January 30, 2016 at 4:40 pm

  #399650

  I'm playing with some sample data and trying to get my head around DAX. ("Abandon hope, all ye who enter here!") ... which seems to be a failing effort... Basically I have

  Patient--(1,M)---Experiences---(M,1)---Symptom.

  Simple table defs...

  CREATE TABLE Patient (PatientID INT IDENTITY PRIMARY KEY ...);

  CREATE TABLE Symptom(SymptomID INT IDENTITY, SymptomName VARCHAR(20) NOT NULL);

  CREATE TABLE Experiences(ExperienceID INT IDENTITY, PatientID, SymptomID

  CONSTRAINT fkPatientID FOREIGN KEY PatientID REFERENCES Patient(PatientID),

  CONSTRAINT fkSymptomID FOREIGN KEY SymptomID REFERENCES Symptom(SymptomID));

  In my dataset, I have only 3 symptoms, 'A','B', 'C'. What I am trying to do is figure out the counts of combinations of symptoms. For example, for these sets, show the count.

  ('A','B') ==> show count where these two exist.

  ('A','C')

  ('B','C')

  ('A','B','C')

  Do I do two columns of Symptom and then a DISTINCTCOUNT?

  Thanks!

  Pieter

  Doing counts for single symptoms is really easy, but I was wondering how to do a filter for two or three. In T-SQL, I could do something like this using something like

  SELECT p.PatientID

  FROM Patient p

  WHERE EXISTS(SELECT 1 FROM Experiences e WHERE e.PatientID = p.PatientID AND e.SymptomID = 'A')

  AND EXISTS (SELECT 1 FROM Experiences e WHERE e.PatientID = p.PatientID AND e.SymptomID = 'B')