Forum Discussion

BradBuske's avatar
BradBuske
Regular Visitor
3 years ago
Solved

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

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...
  • OwenAuger's avatar
    OwenAuger
    3 years ago

    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's avatar
BradBuske
Regular Visitor
3 years ago

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's avatar
BradBuske
Regular Visitor
3 years ago

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

  • OwenAuger's avatar
    OwenAuger
    Icon for Super User rankSuper User
    3 years ago

    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's avatar
      BradBuske
      Regular Visitor
      3 years ago

      Hi Owen,

       

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

       

      Brad