Forum Discussion
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)
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/ESusooBtRdpMvot1Gs85TtgBmVBFO5WiECQPp3cpSp_Exg?e=bMuE6G
@USERELATIONSHIP, @RELATED
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.
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.
3 Replies
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.
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!
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.
