ISFIRSTOCCURRENCE returns duplicates due to extra dimension

Hi ALL,

As shown in the screenshot:

Columns are Locations and D2 Roles. Each Location is mapped to a Final Region Set (e.g., Toronto).The module is dimensioned by Locations and D2 Roles.

I want to export this mapping to another model, but in the target module, I don’t need the D2 Roles dimension.

When I try to use ISFIRSTOCCURRENCE(Final Region Set, Location), it considers all D2 Roles, so the line item is marked for every role. In my target model, I want only one mapping per Location, ignoring D2 Roles.

Notes:Each D2 Role is mapped to the same Final Region Set, so ideally any Location mapped to a region should count as one occurrence.
I cannot hardcode a single D2 Role, because users may map the same Location with different D2 Roles in the future.

Any guidance on how to set this up correctly to get the first occurrence per Location while ignoring D2 Roles?

Accepted answers

All comments

devrathahuja

Hi @Isha ,
One of the ways to get the mapping is:

Create a line item called region working and have it as final region[lookup: abc] (Here the abc is any 1 of the roles).
This will get you your region against the locations and lookup a static role and since you mentioned the mapping is same for all roles it works in your case

Additionally, how is your mapping being maintained if it is same for all roles? In an ideal case scenario, the mapping should then be imported to a SYS location module instead of a module with region and roles.

Isha

@devrathahuja,

Thanks for the suggestion!

One concern is that we cannot rely on a static role, as users may map a Location to any role, and the Region mapping should be derived from wherever the mapping exists, not a predefined role.

For example:

In one scenario, a Location might only be mapped under the Block role to a Final Region Set.
In another scenario, a different Location might be mapped under Compute Role or another role to Final Region set.

Because of this, the Region mapping should be derived dynamically from whichever role contains the mapping, rather than relying on a static, predefined role.

devrathahuja

@Isha - Got it. Do you have summary for the role dimension? Then you can turn on the firstnonblank and select:total roles or show the total roles member in the view instead of a static lookup to get the value.

Also, can you show me what your region set line item is? And what is the difference between region set/final region?

alexpavel

Hi @Isha : but why do you need the Role in the module? Why can't you use a module of the Locations to associate a Region in the source model ?

By inserting the Role in the module together with Locations, you are open to the possibility that one role could choose a different Region from another role for the same Location.

Isha

@alexpavel

That’s a fair question. In an ideal design, if the Location —>Region mapping were truly independent of Role, we would absolutely maintain it in a Location (SYS Location) module.

But in our current setup, the mapping is maintained by users in a Location × Role module because users map Locations under the specific Role they work with. A Location may be mapped under Block in one case, and under Compute or another role in another case, but only one role will have a valid mapping for a given Location.

The Role dimension therefore exists to support how the data is entered and maintained by users, not because we want multiple Region values per Location. Our goal downstream is to derive a single Region per Location based on where the mapping actually exists, and then collapse the Role dimension for export.

Isha

@devrathahuja

Tried using Role summaries with FIRSTNONBLANK, LASTNONBLANKS, but since the mapping exists only at the Role level and not at the summary level, the total view comes back blank.
Even enabling FIRSTNONBLANK, this approach may not be reliable for our use case because the mapping can exist under different roles, and we need to deterministically derive the Region from wherever the mapping actually exists.

You can ignore region set column shown in the screenshot, It's just the code of Final Region set.

devrathahuja

@Isha - To your point on "because the mapping can exist under different roles"

How would firstoccurrence help resolve this in that case? Since effectively it'd also give you only the first value (Similar to firstnonblank)

I assume the mapping at any given time will be present only against 1 role, correct?

Isha

@devrathahuja
Thanks for checking on this.

In our case, the same mapping exists across multiple D2 Roles.

For example, the Equinix location is mapped under roles like Block, Compute, etc., but all of them map to the same Final Region Set (Toronto). So the mapping is not limited to a single role at a time,
the same Location → Region mapping can appear under several roles simultaneously.

Because of this, using ISFIRSTOCCURRENCE (similar to FIRSTNONBLANK) still evaluates across the D2 Role dimension and returns a result for each role, even though the region mapping is identical.

My goal is to identify a single valid mapping per Location, regardless of which D2 Role carries it, since the target model does not include the D2 Role dimension.

devrathahuja

@Isha - Hi,
If it will always be the same, firstnonblank should work fine. An example below-

I have the location for Engineer as A1 which can be against any season but it will always be A1, same for the Vendor supply as A3. Since it will be the same against any, you can set the summary to first nonblank and it will show you A1 at the Total Season member which can then be pulled to the module without season in this case.

For comparison, your location list is the department in the above example & roles is sesason

alexpavel

Hi @Isha .. to identify the first occurrences on cross dimensionality.. you could also use RANK function.. .see below.. and use the filter Rank = 1 to identify the distinct firstoccurrences..

hope it helps

Alex

Isha

@ devrathahuja / @alexpavel

Hi both,

I used this formula and it worked.
ISFIRSTOCCURRENCE(Final Region Set, 'Location') AND ISFIRSTOCCURRENCE(Final Region Set, 'D2 Roles')

Earlier, I tried using the RANK function with:
RANK(1, ASCENDING, SEQUENTIAL, ISNOTBLANK(Final Region Set), Region Set)

While testing, I noticed that one location mapped to a region was assigned rank 16. When I applied a filter to show only rank = 1, that mapping did not appear in the view.

Thank you so much for your guidance and inputs both!

Quick Links

Can you help?

Unanswered questions

Management Reports - Automating PDFs
Hi, we need to bulk export approximately 150 customer Management Reports as PDFs each month from Anaplan. It involves selecting each customer from the context selector, exporting to pdf and then renaming the file. Currently this is a manual process. Has anyone implemented an automated solution to bulk export Management…
Corrupted Import Action
Hi, I'd like to know if my issue already occurred to anyone here. I created a saved view out of a module using different hierarchical lists. In the saved view, I pivoted the dimensions and restricted the view only on a specific line item. Then, I used that saved view to import data into another module however the data…
Finding the lowest element
Hi, I have the following problem to solve and I’m not sure whether it’s even possible in Anaplan. I have List A with a certain number X of elements. For selected elements from List A, based on which I create a subset, I determine the components that make up each element. There can be a maximum of 6 components. Each line…