Forum Discussion

ThomasDay's avatar
ThomasDay
Impactful Individual
9 years ago

ALL() ??

Hello all,   I am beating my head against a wall...and this really doesn't seem like it should be that hard...so I'll turn to the group after a lot of thrashing.   I'm calculating a measure calle...
Need Help
LaurentCouartou's avatar
LaurentCouartou
Solution Supplier
9 years ago

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?

ThomasDay's avatar
ThomasDay
Impactful Individual
9 years ago

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 
ProviderIDSize"Computed visual column" CMI ProviderIDCMISize
2Large1.930 11.950Med
3Med1.940 21.930Large
4Med1.420 31.940Med
7Med1.990 41.420Med
8Small1.540 51.110Med
9Small1.370 61.560Large
11Large1.930 71.990Med
12Large1.400 81.540Small
13SmallMedian of Small 91.370Small
18SmallMedian of Small 101.490Small
19LargeMedian of Large 111.930Large
20LargeMedian of Large 121.400Large

 

Let me know what you think the right structure is and thanks in advance.
Tom

  • LaurentCouartou's avatar
    LaurentCouartou
    Solution Supplier
    9 years ago

    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.

    • ThomasDay's avatar
      ThomasDay
      Impactful Individual
      9 years ago

      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