Dear all,
I am trying to create a measure returning a value based on data form a specific tab in a workbook called "Activity/Waiting Lists", whilst actually being in a dataset that references a different tab, "Estates". i.e. I am trying to drop all the filters applied to the page whilst applying the filters applied to a different page.
The measure below does this quite successfully.
BUT I now need to put this on a chart which uses Providers[NewRegionName] on the x axis.
Of course this doesn't work because the filters are removed! Nor can I use ALLEXCEPT becasue the table the data comes form is different - i.e. Mainfacts and Providers.
Can anyone point me in the right direction please?
best
Steve
ActivityValue = CALCULATE(SUM(MainFacts[Value]), ALL(MainFacts), MainFacts[Tab] = "Activity/Waiting Lists", MainFacts[WLorAct] = "Activity",
Providers[NewRegionName] = "London"||
Providers[NewRegionName] = "North West"||
Providers[NewRegionName] = "South East"||
Providers[NewRegionName] = "Midlands"||
Providers[NewRegionName] = "South West"||
Providers[NewRegionName] = "East of England"||
Providers[NewRegionName] = "North East and Yorkshire",
MainFacts[Attribute] = "Planned tests/procedures completed"||
MainFacts[Attribute] = "Remaining Waiting list tests/procedures completed"||
MainFacts[Attribute] = "Unscheduled/urgent tests/procedures completed",
MainFacts[SubCode] = "1.01"||
MainFacts[SubCode] = "1.02"||
MainFacts[SubCode] = "1.03"||
MainFacts[SubCode] = "1.04"||
MainFacts[SubCode] = "1.05"||
MainFacts[SubCode] = "1.06"||
MainFacts[SubCode] = "1.07"||
MainFacts[SubCode] = "1.08"||
MainFacts[SubCode] = "1.09"||
MainFacts[SubCode] = "1.10"||
MainFacts[SubCode] = "1.11"||
MainFacts[SubCode] = "1.12"||
MainFacts[SubCode] = "1.13"||
MainFacts[SubCode] = "1.14"||
MainFacts[SubCode] = "1.15"||
MainFacts[SubCode] = "1.16"
)
