topic Re: A matter with DAX functions in DAX Commands and Tips

A matter with DAX functions

Anonymous — Tue, 21 Jul 2020 09:43:03 GMT

Hello everyone,

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.

Re: A matter with DAX functions

AllisonKennedy — Tue, 21 Jul 2020 11:11:00 GMT

I'm not sure I understand the question - if you are using cases from the database, then this needs fixed in database. Do you have a DAX measure that calculates "total patients (end of month)" ? If so, please post it here and we can help you update it. If not, please provide name of table and columns and/or measures that you have available in the data model to use for this.

Re: A matter with DAX functions

amitchandak — Tue, 21 Jul 2020 11:55:45 GMT

@Anonymous , I give a solution some time back. See if that can help

https://community.powerbi.com/t5/Desktop/Churn-Rate-lost-Customer/m-p/1173754#M529196

Re: A matter with DAX functions

Anonymous — Tue, 21 Jul 2020 14:37:30 GMT

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?

Re: A matter with DAX functions

Anonymous — Tue, 21 Jul 2020 14:47:21 GMT

Hello amitchandak,

Unfortunately it's not the case this time. Could you think about it as well, please?

Re: A matter with DAX functions

AllisonKennedy — Wed, 22 Jul 2020 06:25:20 GMT

What have you put in 'rows' then?

Is "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!

Re: A matter with DAX functions

Anonymous — Wed, 22 Jul 2020 11:29:01 GMT

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!

Re: A matter with DAX functions

AllisonKennedy — Wed, 22 Jul 2020 11:48:04 GMT

Aha, 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.

Re: A matter with DAX functions

Anonymous — Wed, 22 Jul 2020 14:21:15 GMT

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!

Re: A matter with DAX functions

AllisonKennedy — Thu, 23 Jul 2020 09:41:34 GMT

The || 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.