Distinct resident count as per start and end date selected in date slicer

[ankita_7]

I have a table that has columns like incident_date, resident name , resident ID and hospital name. I have made a measure that calculates total distinct visit count of a resident using its resident ID for overall period in the hospital. But it is giving me the sum of the visit count for that resident when I select 1/1/2020 to 31/1/2020 in date slicer where I have used incident_date as date. When I select Month of Jan, I want it to show me distinct count of him visited that hospital in only month of jan.If I select 2 months i.e. Jan and Feb in the date slicer then the count should give me sum of the distinct count of that resident visited the hospital in two months.I'm using incident_date in date slicer as well as in the visual. 

Can anyone please guide me for what changes need to be made in my logic? or Please can anyone give me a perfect solution for this problem statement?

Measure used below:

All Count = CALCULATE(COUNT('table'[ResidentID]),ALLEXCEPT('table',

'table'[Resident]))

Thanks in advance.

[v-zhenbw-msft]

Hi @ankita_7 ,

How about the result after you follow the suggestions mentioned in my original post?
Could you please provide more details or expected result about it If it doesn't meet your requirement?

Best regards,

Community Support Team _ zhenbw

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

[d_gosbell]

We don't really have enough information to answer this question properly. You talk about counting visits, but you are using something called incident_date. What is the difference between an incident and a visit? Are they the same thing?

If your table has one row per visit, then the simplest measure would be something like

Visit Count = COUNTROWS( 'table' )

I'm not sure why you are currently using ALLEXCEPT as that will strip out the filters on your date column.

The fastest way to get your question answered would be to provide a small set of example data and the results you would expect to see from that data (see How to Get Your Question Answered Quickly - Microsoft Power BI Community )

[v-zhenbw-msft]

Hi @ankita_7 ,

We can use the following measure to meet your requirement.

All Count = 
CALCULATE (
    COUNT ( 'table'[ResidentID] ),
    FILTER (
        ALLSELECTED ( 'table' ),
        'table'[Resident] IN DISTINCT ( 'table'[Resident] )
    )
)

Then we can add a date slicer to control it, the result like this,

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that we have shared?

BTW, pbix as attached.

Best regards,

Community Support Team _ zhenbw

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

[ankita_7]

Hi @v-zhenbw-msft and @d_gosbell ,

We are getting value using this below measure but I'm not getting the proper count.  When some incident happens then that resident visit the hospital so inc_id for resident id and for different incident if he is visiting the hospital then he gets different ID but he visits hospital for same incident/ illness then same ID is entered with date and time of his visit.

Measure used is: COUNTX (     FILTER ('table', EARLIER ('table'[inc_id]) = 'table'[inc_id]),     'table'[inc_id] )

Can anyone help me if the measure is wrong or guide me with the changes required?

Thanks for help in advance.

[v-zhenbw-msft]

Hi @ankita_7 ,

We can use the following measure to rewrite your measure.

Measure = 
var ID_ = MAX('Table'[inc_id])
return
COUNTX ( FILTER ('table', 'Table'[inc_id]=ID_),'table'[inc_id] )

If it doesn’t meet your requirement, could you please provide a mockup sample  based on fake data or describe the fields of each tables and the relations between tables simply?

It will be helpful if you can show us the exact expected result based on the tables.

Please upload your files to OneDrive For Business and share the link here. Please don't contain any Confidential Information or Real data in your reply.

Best regards,

Community Support Team _ zhenbw

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

[d_gosbell]

Sorry, but I find the description of your scenario very confusing. If you could post a small set of example data (as per How to Get Your Question Answered Quickly - Microsoft Power BI Community ) and the results you'd expect from it I'm sure we could resolve your issue quickly. It does not need to be real data, you can make up 5-10 rows just to illustrate your issue.