I face a specific problem. As you can see the "total patients (start of month)" are 1910. Add to those the "new patients in program", who are 48, and then the "total patients (end of month)" are 1958. The problem is that in the last number (1958) the "Drop Outs" number, which is 24, should have been deducted and, thus, it should have been 1934. How can I achieve that? For these numbers I should note that I do not use a specific formula/function but it is just the count of patients for each case from our database.
I would be really grateful if you could help me solve that.
Thanks in advance.
Thanks for your interest.
I am just taking the data from the database without having any DAX measures that calculate both of them, including "total patients (end of month)". I use the table "DiseaseGrps" and from there the one titled "Disease Group", which I added on Columns. Moreover, I use the table "Personalized_Compliance" and from there the one titled "PatientID", which I added on Values as "Count of PatientID".
I assume that I will have to create a measure so as to do what I described on my post, is that right?
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!
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!
Check out the November 2023 Power BI update to learn about new features.
Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.
130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.