Distinct resident count according to date slicer

[ankita_7]

I have a table with columns as resident name, resident ID, hospital name and visited date of every resident. In date slicer I'm using visited date. If I select 1/1/2020 to 1/31/2020 then I should get only that period distinct visited count in that hospital of that resident using resident ID. I 'm using a measure to calculate this count but I'm getting sum of the count for that resident ID in its overall visited period in the hospital and not according to date slicer.

Like if I select two months i.e date from 1/1/2020 to 2/29/2020  then it should give me sum count of that resident ID in that hospital for only two months.

Can anyone help me with this problem?

Thanks in advance.

[Anonymous]

HI @ankita_7,

It sounds like you want to achieve an indirect filter based on the first selection.
If this is a case, I'd like to suggest you extract all resident id to create an unconnected table and use it as the source of the slicer.
You can write a measure to compare current row contents and selected date range(selected resident id date range) and return tag.  After these steps, you can use this measure on 'visual level filter' to filter records.

Measure =
//selector is a new table with all Resident id
VAR selected =
    ALLSELECTED ( selector[ID] ) 
//extract date range based on selected id
VAR dateRange =
    CALCULATETABLE (
        VALUES ( Resident[Date] ),
        FILTER ( ALLSELECTED ( Resident ), Resident[ID] IN selected )
    )
RETURN
    CALCULATE (
        COUNTROWS ( VALUES ( Resident[ID] ) ),
        FILTER ( ALL ( Resident ), Resident[Date] IN dateRange )
    )

If above not help, please share some dummy data with the expected result to help us clarify your scenario and test to coding formula on it.
Regards,

Xiaoxin Sheng

[Anonymous]

Can you post the measure you're using?

[ankita_7]

Measure I'm using to count is:

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

'table'[Resident]))

[Anonymous]

@ankita_7, in the measure you're using, ALLEXCEPT, is removing all of the filters except for those applied to 'table'[Resident] 

Try using the measure below without the ALLEXCEPT function.

All Count = COUNT('table'[PatientID])

or if the ALLEXCEPT function is needed for other reasons, add the date column to the ALLEXCEPT function.

All Count = CALCULATE(COUNT('table'[PatientID]),ALLEXCEPT('table','table'[Resident],'table'[visited date]))

[ankita_7]

Okay. I tried this one. But its not taking the date column.

[Anonymous]

What error are you getting? The ALLEXCEPT function should be able to take multiple columns.

[ankita_7]

It wasn't showing me the date column name and other column it took i.e. patient ID.