Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
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/ESusooBtRdpMvot1Gs85TtgBmVBFO5WiECQPp...
@USERELATIONSHIP, @RELATED
Solved! Go to Solution.
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.
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!
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
73 | |
71 | |
55 | |
38 | |
31 |
User | Count |
---|---|
71 | |
65 | |
60 | |
50 | |
45 |