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.  

  1. I'm calculating a measure called CMI for each RowID in a visual table...there is (1) CMI value for most but not all of the (M) RowID's.
  2. For each row, I use the related CMI if it exists.
  3. If it is not found, I want to use the Median CMI for all RowID's with the same Size attribute.
 
That's it.  I cannot for the life of me get 3) to work.
 
Here's the file structure:
And here's what I've been circling around in code:  
The MedianForSize does not return a thing...always a blank.  I was hoping the All() function would get me out of the row context of the visual and then the next filter using the [Size]on the one side = [Size] from the many side would work properly. (I make it a scalar using VALUES)
CMI v3 = 
//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])))
//And if zero for some reason--set to blank
				VAR FinalMedianForSize = IF(MedianForSize > 0, MedianForSize, BLANK())
				RETURN
//Use the relationship from the row to find the related CMI--use Median to make a scalar for DAX
				IF(MEDIAN(Standardizing_file[CMI]) > 0, 
					MEDIAN(Standardizing_file[CMI]),
					FinalMedianForSize)
Any help would be appreciated,
Tom
Need Help

10 Replies

  • Sean's avatar
    Sean
    Community Champion
    9 years ago

    How about this?

     

    MI v3 =
//First calculate the median of for similar size Providers 
VAR MedianForSize =
    CALCULATE (
        MEDIAN ( Standardizing_file[CMI] ),
        ALL ( Standardizing_file[Size] ),
        VALUES ( HOSP10_2014_RPT[Size] )
    ) //And if zero for some reason--set to blank
VAR FinalMedianForSize =
    IF ( MedianForSize > 0, MedianForSize, BLANK () )
RETURN
    //Use the relationship from the row to find the related CMI--use Median to make a scalar for DAX
    IF (
        MEDIAN ( Standardizing_file[CMI] ) > 0,
        MEDIAN ( Standardizing_file[CMI] ),
        FinalMedianForSize
    )
    • ThomasDay's avatar
      ThomasDay
      Impactful Individual
      9 years ago

      SeanHi Sean--still no joy.  Every row has a proper "Size" column" as you can see below, three rows do not have values to pull from the CMI standardizing file, and the fall back of using the median of the standardizing file for the same sizes yields blanks.  Tom

      It is as if the Filtering using the Values scalar needs to be nested in the All() but I'll be darn if I can figure that out.

      • v-ljerr-msft's avatar
        v-ljerr-msft
        Microsoft Employee
        9 years ago

        Hi ThomasDay,

         

        Could you try the formula below to see if it works?:smileyhappy:

         

        CMI v3 =
//First calculate the median of for similar size Providers 
VAR currentSize =
    VALUES ( HOSP10_2014_RPT[Size] )
VAR MedianForSize =
    CALCULATE (
        MEDIAN ( Standardizing_file[CMI] ),
        ALL ( Standardizing_file ),
        ( Standardizing_file[Size] = currentSize )
    ) //And if zero for some reason--set to blank
VAR FinalMedianForSize =
    IF ( MedianForSize > 0, MedianForSize, BLANK () )
RETURN
    //Use the relationship from the row to find the related CMI--use Median to make a scalar for DAX
    IF (
        MEDIAN ( Standardizing_file[CMI] ) > 0,
        MEDIAN ( Standardizing_file[CMI] ),
        FinalMedianForSize
    )

         

        Regards

  • LaurentCouartou's avatar
    LaurentCouartou
    Solution Supplier
    9 years ago

    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).

    • ThomasDay's avatar
      ThomasDay
      Impactful Individual
      9 years ago

      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

      • 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?