Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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"
)
Solved! Go to Solution.
@SteveIOW . Try like below, Also try to use In
example
Providers[NewRegionName] in{ "London", "North West"}
Try like
ActivityValue = CALCULATE(SUM(MainFacts[Value]), Filter(ALL(MainFacts), MainFacts[Tab] = "Activity/Waiting Lists", MainFacts[WLorAct] = "Activity"
&& (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") ),
Filter(Providers
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"))
)
@SteveIOW . Try like below, Also try to use In
example
Providers[NewRegionName] in{ "London", "North West"}
Try like
ActivityValue = CALCULATE(SUM(MainFacts[Value]), Filter(ALL(MainFacts), MainFacts[Tab] = "Activity/Waiting Lists", MainFacts[WLorAct] = "Activity"
&& (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") ),
Filter(Providers
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"))
)
Amazing - thank you. And thanks for the tips.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
88 | |
82 | |
66 | |
64 | |
57 |
User | Count |
---|---|
171 | |
111 | |
109 | |
72 | |
71 |