cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
jbarta Frequent Visitor
Frequent Visitor

DAX Help!

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. 

1 ACCEPTED SOLUTION

Accepted Solutions
Drors Member
Member

Re: DAX Help!

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.

 1.PNG

7 REPLIES 7
RMDNA Senior Member
Senior Member

Re: DAX Help!

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 Frequent Visitor
Frequent Visitor

Re: DAX Help!

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 Super Contributor
Super Contributor

Re: DAX Help!

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.

Drors Member
Member

Re: DAX Help!

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.

 1.PNG

jbarta Frequent Visitor
Frequent Visitor

Re: DAX Help!

I think this will work. Thanks for responding!

jbarta Frequent Visitor
Frequent Visitor

Re: DAX Help!

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

jbarta Frequent Visitor
Frequent Visitor

Re: DAX Help!

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:

SSAS Model.png

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.

 

 

 BHC Appt Report.png

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

PBI Community Highlights

PBI Community Highlights

Check out what's new in the Power BI Community!

Top Ideas
Users Online
Currently online: 19 members 700 guests
Please welcome our newest community members: