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
dkernen
Resolver II
Resolver II

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

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

2 ACCEPTED SOLUTIONS
jdbuchanan71
Super User
Super User

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

jdbuchanan71_0-1621024295830.png

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

View solution in original post

jdbuchanan71
Super User
Super User

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.

View solution in original post

3 REPLIES 3
jdbuchanan71
Super User
Super User

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

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

jdbuchanan71_0-1621024295830.png

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

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

 

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.