Forum Discussion
A matter with DAX functions
Community ChampionIs "total patients (end of month)" a measure or column in your database? If you can upload screenshots of the Fields pane that might help. 🙂
If they are measures, just do new Measure:
MyPatientCount (end of month) = [Total Patients (End of Month] - [Drop Outs]
Sorry for all the questions, but I'm still a little unclear on what the report looks like.
Thanks!
Allison thank you so much for your interest and your kindness to help me!
I have put nothing in "rows" as you will see below.
I am uploading photos so as to make you have a clear view.
I have deleted some "names" because of personal data matters (for example look at the filter "CP_Description is ......", in which I had a confidential piece of information).
Thanks in advance!
- AllisonKennedyAha, of course, you are using text boxes for the labels.
Right, so your data is:
table "DiseaseGrps" with column "Disease Group"
table "Personalized_Compliance" with column "PatientID"
Table name unknown with column "RecordDate" and table name unknown with column "Status"
I can't see all the filters on Status, but you can build measures for your requirements using DAX filters, for example:
Drop Outs = CALCULATE(COUNT( Personalized_Compliance[PatientID]), FILTER(Personalized_Compliance, Personalized_Compliance[Status] = "Drop-out" || Personalized_Compliance[Status] = "Com"), Month(Today())=Month(Personalized_Compliance[RecordDate]))
adjust accordingly for your data and filter requirements.Thank you so much Allison!
Yes, I am using text boxes for the labels.
Correct! Table "DiseaseGrps" with column "Disease Group", table "Personalized_Compliance" with column "PatientID", table "Patients" with column "RecordDate" (or, more specifically, "RecordDate (Month)"). Lastly, table name "Personalized_Compliance" with column "Status".
As far as the filters on Status are concernced, take a look below please:
Now about Dax filters that you mention. Do you mean that I should make new measures (double click on "fields", then choose "new measure" and then build the function), right?
You gave an example:
Drop Outs = CALCULATE(COUNT( Personalized_Compliance[PatientID]), FILTER(Personalized_Compliance, Personalized_Compliance[Status] = "Drop-out" || Personalized_Compliance[Status] = "Com"), Month(Today())=Month(Personalized_Compliance[RecordDate]))
That was one expression like one line, right? It doesn't accept it. The symbol || is right?
Sorry for insisting on that but I am trying hard to figure out how I can just add the total patients (start of month) [line 1] to new patients in program [line 2] minus the drop outs. Then the "Total patients (end of month" will be correct.
Thanks again for your help!
- AllisonKennedyThe || is an 'or' operator for the function and yes, all one line.
The problem with the example I gave is that the table names don't match yours, and Status is in a different table, so you can't FILTER on status. You can maybe do this instead:
Drop Outs = CALCULATE(COUNT( Personalized_Compliance[PatientID]), Personalized_Compliance[Status] = "Drop-out" , Month(Today())=Patients[RecordDate])
Create a similar measure for Start of Month and New and then simply add/subtract all the measures to get End of Month
If that doesn't work, please paste sample data tables or upload sample pbix so we can work with the actual columns and tables.