DAX Help!

[jbarta]

I am new to Power BI and the DAX language. I have been working with SQL server and T-SQL for almost 10 years now and my skills in DAX are not at the same level as T-SQL. I have been asked to create a graph that groups patients by the number of appointments that have had with our Behavioral Health Consultants (BHCs). They have requested 6 different groups: 1, 2, 3, 4, 5 , 6 plus. So basically they want to know how many patients had 1 appointment, how many had 2 appointments, etc. On top of that, they also want to slice this by Provider and by time period (Year, Quarter, Month, Day). I do have a calendar table with a relationship to the ApptDate in the dataset below.

Data Set:

PatientProfileId	ApptDate	Provider	BHCArrivedAppt
19141	1/15/2015	Test, Provider1	1
19355	4/1/2014	Test, Provider2	1
19450	5/14/2014	Test,Provider3	1
19450	6/16/2014	Test,Provider3	1
19450	6/24/2014	Test,Provider3	1
19450	7/30/2014	Test,Provider3	1
19780	12/17/2014	Test, Provider2	1
21932	7/16/2014	Test, Provider2	1
22067	12/9/2014	Test, Provider1	1
22200	6/20/2014	Test, Provider2	1
22759	8/25/2014	Test, Provider1	1
23512	1/16/2015	Test, Provider2	1
26147	7/21/2014	Test, Provider1	1
26147	5/30/2014	Test, Provider1	1
26147	6/25/2014	Test, Provider1	1
28240	8/19/2014	Test, Provider2	1

If anyone is willing to point me in the right direction with this I would appreciate the help very much. 

[Drors · Accepted solution]

Hi,

You can do it with a summerized table

create new dax table :

SummerizedTable = SUMMARIZE(Table1,Table1[PatientProfileId],"Counter",COUNTROWS(Table1))

after that you have to make a relationship between the summerizedtable and your original table.

Then you just need to create new chart put the "counter" column in the x axis and in the value put the patienID and change it to count (if you take the column from the original table make it count distinct).

after you can add any filter you want.

 

[jbarta]

This almost got me to the solution I was looking for. But the filters or slicers that I apply do not filter the data down as I expect. For example, when I run it with a relative date slicer for the last 6 months, I would expect the summary table to count only those appointments in the last 6 months for each patients. Instead, it is filtering to the patients seen in the last 6 months, but it is counting all of the appt dates that they have had regardless of the date slicer. So to fix this I tried three tables one acting as a bridge between the appointments and the patients. This way I could hopefully filter by date and get just the appointments in that time. My small model looks like this:

All three were created with a summarizecolumns function. The first table was this:

=SUMMARIZECOLUMNS (
Appointments[AppointmentsId],
Appointments[PatientProfileId],
Appointments[ProviderResource],
Appointments[ApptDate],
Appointments[BehavioralApptCt],
Patients[ResponsibleProvider],
FILTER ( Appointments, Appointments[BehavioralApptCt] = 1 )
)

Bridge table was this:

=SUMMARIZECOLUMNS(

BHCFidelityAppts[AppointmentsId],

BHCFidelityAppts[PatientProfileId],

FILTER(BHCFidelityAppts,BHCFidelityAppts[BehavioralApptCt] = 1)

)

And final summary table was this:

=SUMMARIZECOLUMNS(

BHCFidelityBridge[PatientProfileId],

"BHCApptCounter",COUNT(BHCFidelityBridge[AppointmentsId]))

 My final report is below. I created the categories by adding a calculated column to the BHCFidelity table using this dax:

=IF([BHCApptCounter] >= 9, "9 plus", FORMAT([BHCApptCounter],0))

But as you can see in the report, the BHCApptCounter is higher than the count of AppointmentsId from both the BHCFidelityAppts and BHCBridge tables. I cannot get the final summarized table to filter based on appt date. Any help or another approach to this would be greatly appreciated.

 

[jbarta]

I think this will work. Thanks for responding!

[RMDNA]

Hi @jbarta,

What you're looking for are mostly native features of Power BI - DAX/M usually isn't needed for basic data modeling like this. I've mocked up a PBIX for you to demonstrate how this kind of report would be built. Take a look and feel free to come back with any questions you might have.

[jbarta]

I appreciate you mocking that up and taking the time to respond. I do know enough Dax and about Power BI Desktop to create those things. The thing I am struggling with is going to the next step. I need to know how many patients in a giving time period had 1 appointment, how many had 2 appointments, etc. Then they would like me to create a column graph for the with six columns:

1st column for the count of patients with 1 appointment

2nd column for the count of patients with 2 appointments

3rd column for the count of patients with 3 appointments

4th column for the count of patients with 4 appointments

5th column for the count of patients with 5 appointments

6th column for the count of patients with 6 or more appointments 

So basically grouping the patients by appointment count, but still slicing by provider and appt date.

[v-xjiin-msft]

Hi @jbarta,

=> I need to know how many patients in a giving time period had 1 appointment, how many had 2 appointments, etc.

I still not quite understand about the logic of your requirement. How to get these 1 appointment or 2 appointments, etc? 

Since you have already shared us some sample data. What's your expected result based on this sample data? With expected result, it'll lead us the right direction.

Thanks,
Xi Jin.

[jbarta]

Thanks for responding! I did receive another post that pointed me in the right direction.