Working with an appointment database trying to get to how many referrals come into the office based on cases per client.
IE 1 client has 2 cases that should be 2 referrals.
Distinct count on the client gives me a count of 17 referrals yet I want this data to show 18 as the one client has 2 cases.
Doesn’t help that there’s overlap on the case name(s) so the distinct count on case name results in 10.
Attached PBIX has sample data showing how the distinct counts work for referrals and case names but the end result I want is 18 the client referral count.
Is there a way to do this ‘math’ in dax or do I need to create some sort of ‘primary calculated’ field to get the distinct referral data to calculate correctly?
Thanks this is what I am looking for.
A couple follow up questions to make sure I understand the logic for my own educational purposes.
You created a client table with the variable but then you did the same logic within the formula for the new measure.
Was the table creation a step to confirm you’d get the correct result or is it needed along with the DAX measure?
