cancel
Showing results for 
Search instead for 
Did you mean: 

Lookup function is not working

pruizpascual
Explorer
277

Hello all, I'm trying to create a kpi that ignores all page/story filters, until now I was using a calculated account with all dimensions under "enable constant selection" but, to avoid repeating the same calculated account again and again, I tried creating a LOOKUP formula in the model:

The issue is it doesn't work, if I filter something in the screen, both the LOOKUP and the regular accounts will be filtered:

Any idea on what is the issue there? Thank you in advance.

Accepted Solutions (0)

Answers (2)

Answers (2)

DebjitSingha
Active Contributor

Hello pruizpascual,

This is by design. Lookup function takes into account any story or page filter.

Syntax:

LOOKUP([<account member>], [<POV>], [<Ignore Dimension>])

use of ignore dimensions parameter decides how the result set is dispersed across dimensions (ignoring the mentioned dimensions)

Help Document : Lookup

Above link go over examples and the limitations.

Viewers/ readers - If you find above information helpful, feel free to up-vote (arrows on left side).

-Debjit

N1kh1l
Active Contributor
0 Kudos
pruizpascual

I am not sure why LOOKUP() is respecting filters when you have put all dimensions in the IGNORE parameter of the function. I did a quick test and found that when dimensions used in filter are present in IGNORE list, filter ignores them. See below. GM is a calculated member used to LOOKUP Gross Margin with all dimensions in IGNORE list.

Formula defining GM:

When All Product is selected both Gross Margin and GM return same result (expected).

When Product has a filter set to Alcohol. Gross Margin respects the filter but GM simply ignores it. (Expected)

The behavior looks similar to Constant selection in Restricted Measure Calculation unless I am missing something.

Regards

Nikhil