Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ThomasDay
Impactful Individual
Impactful Individual

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:
FileStructure1.png
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
10 REPLIES 10
LaurentCouartou
Solution Supplier
Solution Supplier

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

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

Sean
Community Champion
Community Champion

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
Impactful Individual
Impactful Individual

@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

StillNoJoy.png

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?Smiley Happy

 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.