Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!!!
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.
@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))
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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
80 | |
69 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |