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