Forum Discussion

ThomasDay's avatar
ThomasDay
Icon for Impactful Individual rankImpactful 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
Sean's avatar
Sean
Icon for Community Champion rankCommunity 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
Icon for Impactful Individual rankImpactful 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
    Icon for Microsoft Employee rankMicrosoft 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

    • ThomasDay's avatar
      ThomasDay
      Icon for Impactful Individual rankImpactful Individual
      9 years ago

      v-ljerr-msftThanks for sending this along.  I appreciate the comments and the suggestion.  Still no change to what I posted above to Sean, though.  I think there must be an issue with using the data table as a look up table.  Thanks again, Tom