Forum Discussion
ALL() ??
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
Assuming 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