Forum Discussion
dkernen
5 years agoResolver 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 ...
- 5 years ago
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.
- 5 years ago
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
5 years agoSuper 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.