Create a calculated member that excludes one option

Questions & Answers
,
1

Hi,

This is my first question to the forum so please be kind.

I have a Dimension, Locations that has a list of locations where assets are stored
(Location 1, Location 2, Location 3). One of the locations is no longer used (Location 2) and so has no linked data but due to historical data, the Location is still in the filter options when I use the dimension as a page.

I created a new calculated member under the dimension using the aggregate and except functions.

Aggregate(
Except(
[Locations].Members,
{ [Locations].[Location 2] }
)
)

But I still get Location 2 as a option. Any help welcomed.

Cheers,

Gav

2

Any help would be welcomed??

3

It’s in the page, so I would just “search and bookmark” the locations you want and select the ones you want to display.

1 Like
4

Hi @gav.yates

In the Except() function, it is important to define the sets from the same level members:
[Locations].Members includes all dimension members and it means that [Location] default member or [Location].[All Locations] member also is included in this set. The default member by definition also includes member [Location 2]. Because of that, this location appears even if you have excluded it.

Use [Location].[Location].Members instead in the formula to form a set of Location level member only:

Aggregate(
Except(
[Locations].[Location].Members,
{[Locations].[Location 2]}
)
)

You may want to check out the documentation of Except() function, I added a few rows about this there (thank you for the question!).

Best,

Ilze / support@eazybi.com

1 Like