date filter by if any of the date of the group falls in that range, then it pulls the group

[liyi0716]

Hi Everyone,

 I have a patient list, and each line represents each admission. Each line has that patients admission date, name, MRN, and a column for if that pateitns has been admitted multiple times. 

Now I want to build a readmission pateitn list and add a "admission date" filter. However, I want to bring in all previous admissions when one of the admission date falls in the date range.

Eg. i have a patient who was admitted on 1/1, 2/1 and 3/1. When I filter by admitted within "2/28 - 3/2" (3rd admissions qualifies), it can still shows that patients' 1/1 and 2/1 result as well.

I put all the information in a "table" format.

Thank you all!!!

[Gabriel_Walkman]

Hi!

Let's say you table name is 'admissions' and it has two columns:
- [date] (as in admission date)
- [id] number to identify the patient.

Now let's duplicate this table as 'admissions2'.

Create a relationship from 'admissions'[id] to 'admissions2'[id].

Now if you use a separate calendar table (you always should), make a relationship between 'calendar'[date] and 'admissions'[date].

In this way, if you add a date slicer that affects 'admissions'[date] (preferably through a 'calendar' table) and display data from 'admissions2' it should work almost as you wanted, though this would also display future admissions.

[Ashish_Mathur]

Hi,

Shares some anonymised data and show the expected result.

[amitchandak]

@liyi0716 , with help from slicer using date table not joined

measure =
var _min = minx(allselected('Date'), 'Date'[Date])
var _max = maxx(allselected('Date'), 'Date'[Date])
var _tab = summarize(filter(Table, table[admission date] <=_max && table[admission date] >=_min), Table[MRN])
return
calculate(count(Table[MRN]), filter(Table, Table[MRN] in _tab))

if date table is joined with  admission date

measure =
var _min = minx(allselected('Date'), 'Date'[Date])
var _max = maxx(allselected('Date'), 'Date'[Date])
var _tab = summarize(filter(Table, table[admission date] <=_max && table[admission date] >=_min), Table[MRN])
return
calculate(count(Table[MRN]), filter(all(Table), Table[MRN] in _tab))

[liyi0716]

Thanks A LOT! I tried to replicate your formula to my dashboard but ran in to some problem. I'm very new to power bi so it may sound like a stupid question. 

The tale that I based off has patient Name, MRN, admission date so I guess it qualifies for " data table is joined with admission date"?

Table name: 2017 Main - Present

Admission date: adate

MRN: MR number

So when I input everything, it shows "too few arguements were passed to the filter function. Do you know how to fix it?

Thanks 

[Icey]

Hi @liyi0716 ,

The two closing brackets are missing.

Best Regards,

Icey

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