Conditional Expression Challenge

[Meret]

Hi,

I am trying to create an expression that calculates a measure depending on criteria defined by the target. The goal is a table that shows all the targets with its measures to check if there are any violations.

The data model consists of two tables connected with an ID and the date as the key. Both tables use the same dimensions and codes, but those fields cannot be connected (because of a consequential multiplication of the data).

Here a simplified example of the problem:

The following formula works fine, if an ID is selected:

sum(if($(='['&$(vField)&']')= CODE, MEASURE))

vField = FIELD

However, the goal is to show all the IDs in the same table without a selection (therefore, set analysis is not an option).

How does the formula need to be changed to acchieve this?

Thank you very much for your help.

[Hatus]

Very interesting! Hope to see the suggestions.

[tresesco]

How do you define the variable? Could you share your sample app?

[Meret]

Hi  Tresesco

Thanks for the fast response. 

The variable is defined as:

vField = FIELD

You can find the app in the attachment.

[tresesco]

Ideally I would resolve this in the data model. However, check if the below expression helps:

sum(if(DIM1= CODE or DIM2= CODE, MEASURE)) 

[Meret]

Thanks for your solution.

Sorry, the example was not clear enough.  There is an updated app in the attachment.

In the real app this approach does not work, because the values in the field CODE are not unique. For example, '100' is also used in other dimensions (see DIM3 in the updated app), where it stands for another criteria.

Also, there are nine different dimensions in the real application and in the future, there may be more, so the field must be  kept variable.