I have a table that stores patient vital information. In that table, there is a "BMI Class" column that shows the class with possible values of Healthy, Overweight, and Underweight and a "Vitals Rank" column that tells me for each patient, which is the latest vital. Rank 1 is the most recent, Rank 2 is the 2nd most recent. When collecting vitals for a patient, they may not enter the required information to calculate BMI, so sometimes the BMI Class field will be null.
I'm trying to create a bar chart that shows the count of patient in each BMI class. To do this, I need to find the most recent (based on lowest rank) non-null entry in the BMI Class Field for each patient. In my bar chart, my dimension is [Vital Signs.BMI Class] and my expression is this:
Here's a sample of what data in my Vitals table may look like. Based on this data, i'd expect to see a count of 2 clients in the Healthy category ([ClientID] 1 & 3) and one count in the Overweight category ([ClientID] 2). I never get any data to return in the chart, so i think there is something wrong with my subquery/search set analysis.