Nested Use Relationship

paivers

Board Regular
Joined
Sep 4, 2009
Messages
64
I have the following measure, which although a bit hairy works well. The problem is that I need to add a second USERELATIONSHIP to bring in another dimension. I know how to do that by adding a ,USERELATIONSHIP in each filter. The problem is I need to do that 8 times, and I have quite a few measures like this. So I was thinking about how I could encapsulate the 2 USERELATIONSHIPS into some kind of "subroutine". I thought I could do some kind of measure with do nothing filter including them, but apart from not being sure of the syntax it probably wouldn't work because as soon as the measure is evaluated the relationships would flip back. Maybe some kind of measure above this that calls this, but how to do that. Or is there an alternative way of expressing this where the switch is called one, but I don't think I can put a switch in a calculation. Any thoughts on how to handle this. Sorry for the format, for some reason a carriage return does not work for me in this editor. - Pete Post Sum of Member Months:=switch([Slicer],
1, (calculate(Bridges[Sum of BTH Member Months],filter(AllSelected(Bridges),Bridges[MemberMonths]>=[Post Min]),USERELATIONSHIP(Bridges[EnrollDate],InterventionDate[iDate]))) -
(calculate(Bridges[Sum of BTH Member Months],filter(AllSelected(Bridges),Bridges[MemberMonths]>[Post Max]),USERELATIONSHIP(Bridges[EnrollDate],InterventionDate[iDate]))) +
(calculate(Bridges[Count of BTH Members]* [Post Max],filter(AllSelected(Bridges),Bridges[MemberMonths]>[Post Max]),USERELATIONSHIP(Bridges[EnrollDate],InterventionDate[iDate]))) -
(calculate(Bridges[Count of BTH Members]*([Post Min] -1),filter(AllSelected(Bridges),Bridges[MemberMonths]>=[Post Min]),USERELATIONSHIP(Bridges[EnrollDate],InterventionDate[iDate]))) ,
2, (calculate(HealthCoach[Sum of HC Member Months],filter(AllSelected(HealthCoach),HealthCoach[MemberMonths]>=[Post Min]),USERELATIONSHIP(HealthCoach[EnrollDate],InterventionDate[iDate]))) -
(calculate(HealthCoach[Sum of HC Member Months],filter(AllSelected(HealthCoach),HealthCoach[MemberMonths]>[Post Max]),USERELATIONSHIP(HealthCoach[EnrollDate],InterventionDate[iDate]))) +
(calculate(HealthCoach[Count of HC Members]* [Post Max],filter(AllSelected(HealthCoach),HealthCoach[MemberMonths]>[Post Max]),USERELATIONSHIP(HealthCoach[EnrollDate],InterventionDate[iDate]))) -
(calculate(HealthCoach[Count of HC Members]*([Post Min] -1),filter(AllSelected(HealthCoach),HealthCoach[MemberMonths]>=[Post Min]),USERELATIONSHIP(HealthCoach[EnrollDate],InterventionDate[iDate]))) ,
BLANK())
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
I did find a solution to this. I removed all the USERELATIONSHIPS from the measure, and then put them in another measure that evaluates that one. The USERELATIONSHIP() takes effect for this measure and all measures evaluated within it. Sorry if I wasted brain cells of people reviewing it, but hopefully this solution will help others with this need.
Post Sum of Member Months:=switch([Slicer],
1, (calculate([Calc Sum of Member Months],USERELATIONSHIP(Bridges[EnrollDate],InterventionDate[iDate]))),
2, (calculate([Calc Sum of Member Months],USERELATIONSHIP(HealthCoach[EnrollDate],InterventionDate[iDate]))),
blank())
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top