What I'm trying to do is supply a group of hospitals in a slicer where the user must select only one. From there, I want to create a calculated field that takes a few stats from the selected hospital & determines those hospitals that are within plus or minus 25% of those stats. Out of that compare group, I need to determine a dynamic average & standard deviation that would change as the compare group changes each time a different hospital is selected.
Data Info: The 'Hospital Info' table has a list of hospitals with a unique identifier. There is a relationship to the 'Full Cost Breakdown' which has each hospital's stats & costs grouped by unique identifier.
So far I have gone down the path of getting a single key stat from a single selection using the following measure:
SelectedStat = IF(ISFILTERED('Hospital Info'[Site]) && HASONEFILTER('Hospital Info'[Site]), FIRSTNONBLANK('Hospital Info'[ADP],0),"0")
[Site] is the unique identifier found in both tables & [ADP] is the stat I would like to select.
Where I am getting into trouble is with the IF logic around determining those hospitals that should be in my compare group. Here is where I am with that calculated column:
CompareGroup = IF(AND('Full Cost Breakdown'[ADPs]>=FILTER('Hospital Info','Hospital Info'[SelectedStat])*(1-.25),'Full Cost Breakdown'[ADPs]<=FILTER('Hospital Info','Hospital Info'[SelectedStat])*(1+.25)),1,0)
My issue: When I place the SelectedStat measure in a table & filter to a single hospital, it does return the ADP field that I want. But when I then try to filter down the compare group with the logic above, instead of comparing each hospital to that one site, it compares each hospital to itself.
I hope someone can help point me in the right direction. I don't have a lot of experience with DAX.
