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