Forum Discussion
ALL() ??
LaurentCouartouThis is a measure which will be used as a column in a matrix or a table...subtotals do not matter. The Row is being set by Hosp10_2014_Rpt table...which has a M-1 relationship on it's RowKey with the standardizing table.
I understand the context from the row issue....I know that there are quite a few rows from the Hosp10_2014_Rpt table that will not have an entry in the standardizing table. So I am trying to compute a median across similarly sized facilities from the entire standardizing file for each row as I step thru...and if there isn't a standardizing file match--use this "same size" median.
I have been trying to do that first as a VAR.
//First calculate the median of for similar size Providers VAR MedianForSize = CALCULATE(MEDIAN(Standardizing_file[CMI]), All(Standardizing_file[Size]), (Standardizing_file[Size] = VALUES(HOSP10_2014_RPT[Size])))
What I imagined this did was set the row context to "all"...then filter to those records of the size of the RowKey in Hosp10_2014_Rpt table using second filter. So in my imagination, I would have a context now of similarly sized records from the entire standardizing file for same sized facilities--and the Median would be CALCULATED. Clearly that's not happening.
Perhaps that's clear enough that you could suggest the proper structure of this.
Thank you,
Tom
Do you mean the following?
- if the current hospital has not CMI, take the median CMI for hospitals of the same size
- if there are more than 1 hospital in the selection, take the median of the previous calculation
Otherwise, could you provide an example with a few rows of data and what the expected results should look like?
OK, here it is: The RPT table needs a computed CMI in it. The Standardizing file is the source of the matched values and the median calcs for provider id's that aren't found in the Standardizing file.
Report visual Standardizing File ProviderID Size "Computed visual column" CMI ProviderID CMI Size 2 Large 1.930 1 1.950 Med 3 Med 1.940 2 1.930 Large 4 Med 1.420 3 1.940 Med 7 Med 1.990 4 1.420 Med 8 Small 1.540 5 1.110 Med 9 Small 1.370 6 1.560 Large 11 Large 1.930 7 1.990 Med 12 Large 1.400 8 1.540 Small 13 Small Median of Small 9 1.370 Small 18 Small Median of Small 10 1.490 Small 19 Large Median of Large 11 1.930 Large 20 Large Median of Large 12 1.400 Large Let me know what you think the right structure is and thanks in advance.
TomAssuming two tables names Hospitals and StandardizingFile, I suggest adding a calculated column to the Hospitals table.
The column should look like this.
= VAR CMI = RELATED( StandardizingFile[CMI] ) VAR MedianForSameSize = MEDIANX( FILTER( Hospitals , [Size] = EARLIER( [Size]) && NOT(ISBLANK(RELATED( StandardizingFile[CMI]))) ) , RELATED( StandardizingFile[CMI] ) ) RETURN IF( NOT(ISBLANK(CMI)), CMI, MedianForSameSize )You can then aggregate this column as want, if you want to make a measure out of it.
LaurentCouartouSorry for the tardy reply--I was completely out of reach of the internet or cell for 9 days. It was pretty great but I'm just back. Thank you for this. Good idea to push to a column and I've not used Earlier so it will be a nice learning experience for me. Thank you for your time and effort.
I've put it into the column and it computes...and I'll need to adapt it a bit. Right now, the medians for each bed size category are not consistent. Let me work with it...it's a nice approach so thank you.
Tom