Forum Discussion
ALL() ??
First, note that All(Standardizing_file[Size]) does not do anything in MedianForSize, since it is already implied by your second context argument.
I am not sure from your post, whether your calculation is a calculated column or a measure.
In the first case, you have to take into account that CALCULATE will use the current row as a filter context for the calculation of the MEDIAN. This means the value in each column on the current row will be added to the filter context (unless you stipulate otherwise in a context argument).
This applies to any column in Standardizing_file, including the [CMI] column.
In other words, if Standardizing_file[CMI], returns BLANK for the current row, MedianForSize will also return BLANK for the current row.
If your expression is meant as a measure (though I would expect errors for subtotals), you would still have the issue that MedianForSize will be BLANK whenever MEDIAN(Standardizing_file[CMI]) is BLANK. Because the latter expression can only return BLANK, if VALUES(Standardizing_file[CMI]) contains no rows or only BLANK values for the current context.
As a conclusion, you should at least have a context argument overriding Standardizing_file[CMI] (and also correlated columns) in your CALCULATE.
Most likely, having All(Standardizing_file) as the first context argument will be enough, but the correct form for your expression may change on whether it is a measure or a calculated column (an in that case, in which table you add it).
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.
Tom
