Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
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,
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).
@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
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?
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
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?
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
User | Count |
---|---|
140 | |
113 | |
104 | |
76 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |