Generating Restricted Columns

A restricted column is a generated column that is made from a measure filtered by one or more attributes values, for example:

• Sales Revenue for France
• Profit for Automotive Parts in 2022

The value in a restricted column is 0 for all rows that do not match the defined filter condition(s).

The benefit of a restricted column is that it provides the modeler with a ready-made object that is useful when you are creating calculated columns as the base objects are already filtered. This simplifies the creation of calculated columns.

Restricted columns can also be used as a base measure to create further restricted columns. For example, if you create a restricted column Quantity for Spain, you could then use this to create two more restricted columns Quantity for Spain in 2021 and Quantity for Spain in 2022. For the second and third restricted columns, you only need to add the additional filter for the individual years. You can create very meaningful and focused columns using this feature. If you change the definition of the underlying restricted columns, those on top will immediately inherit the change.

The modeler should always provide a meaningful description for the restricted column so that the business user understands how the measure has been filtered as the filters are not visible to the user and they rely on column descriptions.

Creating Restricted Columns

Setting the Restriction

If there are different lines in the restriction, all the lines defined on the same column are combined with the logical operator OR, and then, all the sets of restrictions for different columns are combined with the logical operator, AND.

Note

This is the case regardless of the order in which you define the lines.

Displaying and Editing Restriction Expressions

Another option to define or edit the restrictions is to use an Expression. The expression is written in the SQL language. This provides more flexibility when the standard operators for the column-based, graphical restrictions do not fulfill your needs. The expression can use column names, operators, input parameters. Restricted column expressions can also include Functions.

Note

For example, extracting the year YYYY from a date column like YYYY-MM-DD with a string or date function is possible within an expression.