Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I'm not sure if this is best handled in query, or if by a measure, or what.
I have 2 tables: Table 1 is a list of clients, each with a unique Client ID# (MRN), and Table 2 is a list of encounters that happened with a column indicating a condition of "compliant", "non-compliant", or "n/a". An "n/a" means that the item should not be counted for the denominator.
I have merged the two tables into Table 3, so I now have duplicate client IDs due to the multiple encounters. The date relationship is keyed to the EOQ date (trending by quarters)
So - I need to count the number of "compliant", divide by the total of "compliant" + "non-compliant" to get the percent of compliant clients within the quarter, but with a distinct count of the Client ID#. Sample data below:
Thank you!
Solved! Go to Solution.
I believe it is because the same client may have multiple "compliant" conditions within the same quarter. Is there a way to also do a distinct count of the client ID in the portion that counts the condition?
You can create a measure to calculate the percentage of compliant clients within a quarter based on the distinct count of Client ID#:
This measure uses the DISTINCTCOUNT function to get the total number of distinct clients within a quarter. Then, it uses the CALCULATE function with the COUNTROWS function to count the number of compliant encounters within a quarter. Finally, it divides the number of compliant encounters by the total number of distinct clients within the same quarter, and returns the result as a percentage.
You can use this measure in a visual or a table to show the compliant percentage by quarter.
Thank you for the quick response but I don't believe this worked to correctly remove duplicate Client IDs. Any other thoughts?
I believe it is because the same client may have multiple "compliant" conditions within the same quarter. Is there a way to also do a distinct count of the client ID in the portion that counts the condition?