Forum Discussion
Need help aggregating values in a dimension table - using different date relationships
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.
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!