Calculation reference on key column

In the data warehouse, dimensions are defined by a KeyColumn property and a NameColumn.

These two properties may be different, where the KeyColumn property refer to a unique key of the dimension members, and the NameColumn property is used to display the value or the string that the end-user should see.

E.g., the Month dimension, where the KeyColumn would be numbers 1 through 12, while the NameColumn would contain month names January, February ... etc. up to December.

If your calculation needs to refer to the key rather than the name, you must use a special syntax.

Consider this example from an OLAP data model: 

The Item dimension has a Description attribute with different KeyColumn and NameColumn properties:

Note: The No attribute is irrelevant and could have been hidden. It is kept here in order to better explain the example.

In the TARGIT client, Table 1 contains No as well as Description. Notice that 'Glass Door' has Item No '70011'.

Table 2 contains only the Description attribute.

The syntax for the Glass Door Calculation is this:

sum(0, @@"[Item].[Description].&[70011]", 0)

If the data model with a similar dimension had been designed in TARGIT's Data Model Editor (e.g., for InMemory or Relational data sources), the syntax would have slightly different:

sum(0, @@"[Description].&[70011]", 0)