Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
ThThGR
Frequent Visitor

A matter with DAX functions

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.

 

Patients.png

 

I would be really grateful if you could help me solve that.

 

Thanks in advance.

9 REPLIES 9
amitchandak
Super User
Super User

@ThThGR , 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

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

Hello amitchandak,

 

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

AllisonKennedy
Super User
Super User

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.

Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

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?

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!

Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

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.InkedPBIDesktop_CCW9lneoqX_LI.jpg

 

InkedPBIDesktop_iXOBosiqBY_LI.jpg

 

InkedPBIDesktop_rpAwnpkwHI_LI.jpg

 

PBIDesktop_Y7ShJqnaob.png

 

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!

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.

Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

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:

 

PBIDesktop_rOJw6gO4v5.png

 

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!

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.

Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.