I have a large transaction table, CompanySales, with the following important columns in a dataset.
SaleDate
State
Category
SubCategory
SalesAmount
..
other columns which can be added to the filter by the user.
...
ReportContext1
ReportContext2
ReportContext3
I have three separate reports Report1, Report2, and Report3.
Report1 uses a report wide filter ReportContext1 = true.
Report2 uses a report wide filter ReportContext2 = true.
Report3 uses a report wide filter ReportContext3 = true.
Which I set within the respective report itself.
I have a measure which calculates the total sales for the entire state.
StateWideSales = CALCULATE (SUM(SalesAmount), ALLEXCEPT(CompanySales[State]))
The problem I have is that ALLEXCEPT loses the report context. As a result, I start including data in the reports, which was not meant for it. I can fix this issue by rewriting the measure as following:
StateWideSales = CALCULATE (SUM(SalesAmount),
ALLEXCEPT(
CompanySales[State],
CompanySale[ReportContext1],
CompanySale[ReportContext2],
CompanySale[ReportContext3]))
Obviously, the issue with the solution is that I need to remember to do it for every such type of measure.
Secondly, if in future I add a fourth report connected to the same model, I will have to reflect this change in every measure in future.
What will be the best way to address this dilemma?
Thanks!
