cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
collinlorbecki Frequent Visitor
Frequent Visitor

Trouble creating calculated & dynamic fields based on single selection

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. 

 

1 REPLY 1
Highlighted
Super User
Super User

Re: Trouble creating calculated & dynamic fields based on single selection

You should break this problem into parts.  Start by just trying to correctly calculate the stats for the one selected hospital.  You click on the slicer and the data table will then be filtered to only show data for that one hospital.  You can then write a measure that calculates the stats for that one hospital without writing complex "isfiltered" formulas - this is not required.  The slicer provides the filter, so you just write the measure.

 

Can you do this first and post your progress back here?



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.