Within an app I want to add the possibility to compare results based on different filter sets, where each filter set exists of multiple dimensions. However I keep facing the same issue: the result only includes facts which contain values for the dimensions in the set modifier. The result is that I am missing data in my aggregations.
I’ll try to make it more tangible with an example.
Suppose there are three dimensions:
with the following associations:
- Facts - Company- Holding
- Facts - Service
Keep in mind that:
- not all facts contain a service
- not every company has a holding
To get the desired cross section of data, I added for each dimension a table with a copy of all distinct values of that dimension and gave the fields a different name to prevent associations. Thus in this example I actually have he following tables:
- Holding
- Company
- Services
- Facts
- [Pseudo Holding]
- [Pseudo Company]
- [Pseudo Service]
with the associations:
- Facts - Company- Holding
- Facts - Service
Use for example the following set analysis:
{1< Holding = P([Pseudo Holding]), Company = P([Pseudo Company]), Service = P([Pseudo Service]) >}Sum(Revenue)
ignores all revenue for companies without a holding and facts without a service. Is there a way to ignore set modifiers if no values have been selected for the pseudo dimension dynamically (e.g. Service=)? All my attempts have failed so far. I tried adding conditions, variables, multiple set modifiers, intersections, GetSelectedFields(), GetSelectedCount…
