cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper V
Helper V

DistinctCount per startdate

Hi,

 

I have the following issue:

For a hospital I'm creating some dashboards. I want to calculate the amount of incoming patients per day. So, a patient comes in today at 3-12-2019 and stays for 3 weeks. He leaves at 24-12-2019. I want to calculate the amount of incoming (and outgoing) patients per day. But I DON'T want to calculate the complete amount of patients per day.

 

In this image, you'll see one patient incoming at 19-5-2017 at 00:00. I want to calculate this as 1. Then he gets a transfer, at 19-5-2017 10:15 to a different department. He gets a new Patient ID, because this is a new count. The Patient Code doesn't change. You see there's a new startdate. I want to calculate this as 1.

At 22-5-2017 12:45 he leaves the hospital, so there's an enddate. I want to calculate this as 1.

 

For 21-5-2017, I don't want to calculate anything as there isn't a mutation. How do I do this? Something with min() and max() dates? Something with firsdate() ? I think we have to Distinctcount the Patient ID since this is the unique record per row.

 

Thanks a lot!

patient dates.jpg

5 REPLIES 5
Highlighted
Community Support
Community Support

Re: DistinctCount per startdate

Hi @RemiAnthonise ,

 

You can create measure like DAX below.

 

Count=
Var c=CALCULATE(COUNT(Table1[Bereken Van]), FILTER(ALLSELECTED(Table1), Table1[Patient Code]=MAX(Table1[Patient Code])))
Var d= CALCULATE(DISTINCTCOUNT(Table1[Patient ID]), FILTER(ALLSELECTED(Table1), Table1[Patient Code]=MAX(Table1[Patient Code])))
Return
IF(c>2, d+1,d)

 

Best Regards,

Amy

 

Community Support Team _ Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Highlighted
Helper V
Helper V

Re: DistinctCount per startdate

Hi @v-xicai  Amy,

 

Thanks for your reply. This is not exactly what I'm looking for. 

The outcome of your Count gives me 2 where I expect this: the sum of my Count is 23.

 

I marked some rows in red: this is the same Patient (code) but a patient gets transfered. You see a change in the Bereken Van (startdate) and his Patient ID changes.

 

In green, you see a patient without any transfers / changes in dates. He is there for 3 days so I only want to count this one as 1 on the 26th.

 

patient dates.jpg

 

What I want to create here is for all the incoming patients. When we have this I can change it to all the outgoing patients. That should be easy of we can solve this one.

 

Hope this is clear.

Highlighted
Helper V
Helper V

Re: DistinctCount per startdate

Hi @v-xicai Amy,

 

Sorry to bother you with this but if you have time to check my issue, I'd really appreciate it.

Thanks.

Highlighted
Helper V
Helper V

Re: DistinctCount per startdate

Hi @v-xicai or someone else:

 

I'm getting close to the deadline I have for this report and I haven't found the solution yet. Can you help me with this one?

 

Thanks!

Highlighted
Community Support
Community Support

Re: DistinctCount per startdate

Hi @RemiAnthonise ,

 

You can create measure like DAX below.

 

Count=
SUMX (
    SUMMARIZE (
        Table1,
        Table1[Patient Code],
        Table1[Patient ID],
        "Result",
     Var d= CALCULATE(DISTINCTCOUNT(Table1[Patient ID]), FILTER(ALLSELECTED(Table1), Table1[Patient Code]=MAX(Table1[Patient Code])))
     Return
     IF(d>=2, d+1,d)
   ),
    [Result]
)

 

Best Regards,

Amy

 

Community Support Team _ Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
June 2020 Community Highlights

June 2020 Community Highlights

Featured community members, changes to the Community, and more! Read up on recent Power BI community news.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors