Forum Discussion
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.
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.
I think this will work. Thanks for responding!
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.