How to return a median from a calculated table using ADDMISSINGITEMS.

[BradBuske]

I'm trying to create a new measure which will return the daily median of a calculated table.  I continue to get the error "The expression specified in the query is not a valid table expression."  I'm using the ADDMISSINGITEMS function to return zeroes for days where no patients were cared for by a "care team".

Below is the DAX that is receiving this error:

EVALUATE
MEDIANX(
FILTER(
FILTER(
ADDCOLUMNS(
ADDMISSINGITEMS('DATE - CENSUS'[FULL DATE],
'CARE TEAM - CENSUS'[CARE TEAM NAME],
SUMMARIZECOLUMNS('DATE - CENSUS'[FULL DATE],
'CARE TEAM - CENSUS'[CARE TEAM NAME],
"Daily Census Count",[Hospital Encounter Count]),
'DATE - CENSUS'[FULL DATE],
'CARE TEAM - CENSUS'[CARE TEAM NAME]),
"Daily Census Count (With Zeroes)",
IF(ISBLANK([Daily Census Count]),0,[Daily Census Count])),
'CARE TEAM - CENSUS'[CARE TEAM NAME] = "Adolescent Medicine"),
'DATE - CENSUS'[FULL DATE] <> DATE(1900,1,1) &&
'DATE - CENSUS'[FULL DATE] <> DATE(2099,12,31)),
[Daily Census Count (With Zeroes)])

If I remove the MEDIANX function from the above DAX, I get exactly the calculated table I need to calculate the daily median.

Thanks!

[OwenAuger]

@BradBuske Thanks for the update 🙂

Hmm, I can't see anything in your DAX that would result in the median without zeroes.

To help diagnose, could you share a sanitised PBIX file?

Feel free to replace the data with dummy values, as long as we can still see the incorrect results. And add a comment indicating what the correct result should be.

Thanks a lot!

[BradBuske]

Hi Owen.  I'll attach the PBIX file via email.  I was not able to provide "dummy" data ... too time consuming to do that right now.  But, you can see a small version of my model along with the median measure.  It is definitely removing the zeroes when slicing on the "CARE TEAM NAME".  I'll also attach a couple of pivot table reports via email showing the results for the "XZRWROLOTY NP SOSPRTZLRST W" care team (sanitized).  For the month of January 2022, this care team has 22 days where zero patients were treated.  The median should be zero, but instead 6 is the result.  I also tried the PERCENTILEX.INC function ... same result ... 6.  This does work perfect when a care team has no days in a month where 0 patients were treated.  However, my original median measure works too.  I'm trying to improve my original measure to handle zeroes.  Thanks and please let me know if you need add'l info.

[BradBuske]

Hi Owen.  Thank you for the email address.  I will email you the above files.

[OwenAuger · Accepted solution]

Thanks Brad, files received 🙂

I had a quick look and the issue looks to be the bidirectional relationships between the dimension tables and 'HOSPITAL ENCOUNTER DAY HOUR BRIDGE'.

Ordinarily, these relationships should be single-directional with 1-side filtering many-side. The problem with these bidirectional relationships is that the dimensions effectively cross-filter each other.

For example, applying a filter CARE TEAM = "XZRWROLOTY NP SOSPRTZLRST W" limits the visible dates to 21-28 & 31 Jan. This means that dates outside this set are not included in the Median calculation.

Can you try changing those three relationships to single-directional (as shown below)? Leave the relationship between 'HOSPITAL ENCOUNTER DAY HOUR BRIDGE' and 'HOSPITAL ENCOUNTER' as bidirectional as I can see that is required.

I believe that should fix the behaviour of the Median measure. Does it work at your end?

Regards

[BradBuske]

Hi Owen,

You are "spot on" with your fix.  This works perfectly now!  Thank you so much for your assistance.

Brad

[OwenAuger]

Hi @BradBuske 

On the immediate issue:

A DAX query must return a table, not a scalar value, which is the reason for the error message.

If you want to see the value of a scalar expression using a DAX query, you must include it in a table of some sort.

For example, you could convert your current expression into a table with this query:

EVALUATE
VAR MedianValue =
    MEDIANX (
       // ...
    )
RETURN
    { MedianValue } -- use simple table constructor

    -- Alternatively use ROW function
    -- ROW ( "Median", MedianValue ) 

However, a measure to be used in Power BI visuals must return a scalar value, so your final measure could be:

Median Measure =
MEDIANX (
    // ...
)

Use of SUMMARIZECOLUMNS and ADDMISSINGITEMS:

I would generally be cautious of using SUMMARIZECOLUMNS in measures (see here). It may work in some scenarios but can throw errors if referenced within other measures e.g. within iterators with context transition.

A possible re-writing of your measure with a few other tweaks to give the same result.

(I have not been able to test so may require some tweaking):

MedianMeasure =
CALCULATE (
    MEDIANX (
        -- CROSSJOIN will return all combinations regardless of
        -- existence in fact table
        CROSSJOIN (
            VALUES ( 'DATE - CENSUS'[FULL DATE] ),
            VALUES ( 'CARE TEAM - CENSUS'[CARE TEAM NAME] )
        ),
        COALESCE ( [Hospital Encounter Count], 0 )
    ),
    -- Wrap filters in KEEPFILTERS in order to intersect with existing filters
    KEEPFILTERS ( 'CARE TEAM - CENSUS'[CARE TEAM NAME] = "Adolescent Medicine" ),
    KEEPFILTERS (
        NOT 'DATE - CENSUS'[FULL DATE] IN { DATE ( 1900, 1, 1 ), DATE ( 2099, 12, 31 ) }
    )
)

Regards

[BradBuske]

Thank you, Owen.  This helped.  I'm getting results without errors in either Excel or Power BI now.  However, I'm getting strange results when slicing on this measure.  Below is my improved DAX:

ROW("Daily Census Median",
MEDIANX(
CROSSJOIN(
DISTINCT(FILTER(SELECTCOLUMNS('DATE - CENSUS',"Full Date",[FULL DATE]),NOT [Full Date] IN {DATE(1900,1,1),DATE(2099,12,31)})),
--DISTINCT(FILTER(SELECTCOLUMNS('DATE - CENSUS',"Full Date",[FULL DATE]),STARTOFMONTH('DATE - CENSUS'[Full Date]) = DATE(2023,1,1))),
DISTINCT(FILTER(SELECTCOLUMNS('CARE TEAM - CENSUS',"Care Team Name",[CARE TEAM NAME]),[Care Team Name] = "Adolescent Medicine")),
DISTINCT(FILTER(SELECTCOLUMNS('UNIT TRANSFER STATUS - CENSUS',"Unit Presence Group",[UNIT PRESENCE GROUP]),[Unit Presence Group] = "In unit"))),
COALESCE([Hospital Encounter Count],0)))

When I slice this measure by "CARE TEAM NAME" using either Excel or Power BI, I get the median without the zeroes (incorrect).  If I simply Place the "Daily Census Median" in a report, I get the median with the zeroes (correct).  Why does this happen?  Is there some additional DAX I need to add to correct this behavior?  Note that I'm filtering on "CARE TEAM NAME" for now to simplify testing.  I need this to work for all care teams.