cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
liyi0716
Regular Visitor

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

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!!!

5 REPLIES 5
Gabriel_Walkman
Resolver III
Resolver III

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.

Hi,

Shares some anonymised data and show the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
amitchandak
Super User
Super User

@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))



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

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?

liyi0716_0-1623699860059.png

 

Thanks 

Icey
Community Support
Community Support

Hi @liyi0716 ,

 

The two closing brackets are missing.

 

liyi0716_0-1623699860059.png

 

 

Best Regards,

Icey

 

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

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!