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
- The restrictions for a restricted column do not have to be limited to one single Column: you can filter based on multiple columns depending on your reporting requirements.
- Multiple operators can be used for the filter restrictions, such as Equal, Greater Than, Less Than and others.
- You have the option to hide the restricted column, for example to reuse it in a calculated column and thereby make it unavailable for reporting.
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.
- Several restrictions on the same column are combined with an OR operator.
- Restrictions on different columns are combined with an AND operator.
- To visualize the expression, select the Columns tab.
- You can also edit the expression to enrich it.
In this case, the column-based definition can no longer be edited.
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.