How to dynamically ignore set modifiers if no selections have been made for those pseudo dimensions

AntonBakker — Fri, 16 Dec 2022 07:32:59 GMT

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:

Holding
Company
Service

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…

Re: How to dynamically ignore set modifiers if no selections have been made for those pseudo dimensions

AntonBakker — Sat, 17 Dec 2022 19:19:21 GMT

Found the solution in this artikel: How to pass IF statement inside Set Analysis modifiers?

When using an if statement, you have to wrap the then and else statement in single quotes and it works. Thus:

{1<

$(=if(GetSelectedCount([Pseudo Holding]) = 0, 'Holding =','Holding = P([Pseudo Holding])')),

$(=if(GetSelectedCount([Pseudo Company]) = 0, 'Company =','Company = P([Pseudo Company])')),

$(=if(GetSelectedCount([Pseudo Service]) = 0, 'Service =','Service = P([Pseudo Service])'))

>}Sum(Revenue

topic How to dynamically ignore set modifiers if no selections have been made for those pseudo dimensions in App Development

How to dynamically ignore set modifiers if no selections have been made for those pseudo dimensions

Re: How to dynamically ignore set modifiers if no selections have been made for those pseudo dimensions