Need help aggregating values in a dimension table - using different date relationships

[dkernen]

Hello!  I need to calculate averages based on values that are part of my dimension.

Primary Tables - dimOrgan - which has the expected and observed number of organ transplants

                        - factCase - which has all my dates and dateIDs

                        - v_dimDate - which has all the date information and has an active relationship and MANY inactive relationships that I need to activate based on the audience

(dimOrgan <one> connects to factCase <many> via Referral_ID)

I need to calculate the observed/expected (OvE) ratio - but the kicker is that I need to create separate measures based on which date is used.  The current measure works - but on the default date.

Expected = CALCULATE(SUM(dimOrgan[ExpOTransplanted]),factCase) - I need this to use the correct date (whatever is relevant)

Observed = CALCULATE(SUM(dimOrgan[ObsOTransplanted]),factCase) - I need this to use the correct date (whatever is relevant)

OvE = DIVIDE([Observed],[Expected])

I think I need a RELATED function, and definitely a USERELATIONSHIP - but I am at a loss as to what to do next.

I have shimmied down my file to show just 2021 data and the necessary tables.  The issue is that when I roll up data by month - it needs to be accurate based on the date selected.  I included examples of cases where the two dates are in different months in my file (on the Notes tab).  Thank you so, so much!

https://mwtn-my.sharepoint.com/:u:/g/personal/dkernen_mwtn_org/ESusooBtRdpMvot1Gs85TtgBmVBFO5WiECQPp...

@USERELATIONSHIP, @RELATED

[jdbuchanan71 · Accepted solution]

@dkernen 

I think I understand what you are trying to get to.  If we use CROSSFILTER in a measure from Case to Organ like this.

dimOrgan SUM =
CALCULATE (
    SUM ( dimOrgan[CCRUNOSObsOTransplanted] ),
    CROSSFILTER ( factCase[Referral_ID], dimOrgan[Referral_ID], BOTH )
)

We can then have USERELATIONSHIP shift the dates like you did in your first measures.

dimOrgan BCR_Date = 
    CALCULATE ( 
        [dimOrgan SUM], 
        USERELATIONSHIP(v_dimDate[DateID],factCase[BCR_DateID]) 
    )

And we end up with something like this.

I have attached my updated version of your file for you to look at.

[jdbuchanan71 · Accepted solution]

It is cleaner if you do create the measure.  It is not really temporary since I can see you using it in other places but it could probably be named better.

[jdbuchanan71 · Accepted solution]

@dkernen 

I think I understand what you are trying to get to.  If we use CROSSFILTER in a measure from Case to Organ like this.

dimOrgan SUM =
CALCULATE (
    SUM ( dimOrgan[CCRUNOSObsOTransplanted] ),
    CROSSFILTER ( factCase[Referral_ID], dimOrgan[Referral_ID], BOTH )
)

We can then have USERELATIONSHIP shift the dates like you did in your first measures.

dimOrgan BCR_Date = 
    CALCULATE ( 
        [dimOrgan SUM], 
        USERELATIONSHIP(v_dimDate[DateID],factCase[BCR_DateID]) 
    )

And we end up with something like this.

I have attached my updated version of your file for you to look at.

[dkernen]

@jdbuchanan71,

WOW!  Thank you.  Thank you for your speediness and your thoroughness.  Wow.

Is it necessary to create the "temporary" [dimOrgan SUM] measure or can it be wrapped within the "secondary" measures [dimOrganOStatus_Date] and [dimOrgan BCR_Date]?  I'd like to use variables, if possible, as I already have so many measures.

Again, thank you so much!