Loading the documentation…
A calculated column is a column you add to a BI table inside a semantic model. Its value is a ClickHouse SQL expression over the warehouse columns of the same BI table, worked out every time a report runs a query; nothing is stored in the warehouse. Use one for a value per row that the warehouse table does not have, such as a margin, a band or a cleaned-up code.
<table> computed from its warehouse columns on every query."Margin. Expressions and measures refer to the column by this name, and it cannot change later. Reports show the same text; to show something else, change the column's label after you save it.<table> without a table name, for example "Amount" - "Cost". Ctrl+Enter saves." Suggestions offer the table's warehouse columns, and a counter shows the size, such as 24 /.Calculated column "<name>" created. Cancel, or Escape in the editor, closes the dialog without saving.A problem with the name shows under Name once you leave the field. A refused save shows its reason in red under the expression; when DataPrism ran the expression to check it, an Executed SQL section shows the statement it ran.
An expression can be as simple as "Amount" - "Cost", or sort rows into bands:
multiIf("Amount" >= 1000, 'Large', "Amount" >= 100, 'Medium', 'Small')You can add a calculated column without leaving the report you are building.
The column joins the semantic model, so every report on the model can use it. It appears in the panel under its BI table, ready to drag into a visual.
You do not have permission to edit this semantic model.The New calculated column and Edit calculated column dialogs carry an AI control beside Expression: describe the value, and the AI writes the expression for you to review.
Age band of the customer: under 30, 30 to 49, 50 and over., and click Generate. The popover carries the column's name, or AI calculated column before you enter one.AI notes toast shows them.Failed to generate the calculated column expression, and the popover stays open for another try. Escape closes the popover and leaves the dialog open.Fix with AI appears beside the reason in three places:
Click it. The AI reads the expression, the reason and the Executed SQL when there is one, and rewrites the expression in the editor; while it works, the button reads Fixing.... The toast AI updated the expression - review and save confirms it and adds the AI's notes when there are any. Run Preview, then click Save. If the AI cannot help, the message reads Failed to fix the calculated column expression and the expression stays as it was.
When a column is red for a reason outside its expression, such as a re-check that has not run yet or a warehouse column that now has its name, use Re-check or delete the column instead (When a calculated column turns red).
The type is detected from the expression each time you save. A calculated column returns a number, text, a date, a date-time, a time, a duration or a true/false value, and starts with that BI type's default summarization and format. Lists, maps, tuples and other composite results are refused. When an edit changes the type, the summarization and the format go back to the new type's defaults.
Calculated column "<name>" updated., and visuals open on the model run again.Each save checks the expression again, even when it did not change. When measures use the column, they are checked against the new expression too: a measure that now fails turns red, and one that works again is cleared.
Everything else about the column is set like any other column: its label, folder, description, format, summarization, Sort by and In report, from the column toolbar in the BI tables panel or in the BI table popup (BI table columns). The popup's Expression column shows the expression read-only, with "Edit the expression with Edit expression in the BI tables tree".
This will permanently delete "<name>". and says what the delete affects: Measures that turn red: <names>. or No measure turns red., and <n> charts in <m> reports use it and stop working until a column named "<name>" exists again. or No chart uses it.Calculated column "<name>" deleted., and adds These measures are red now: <names>. when measures broke.Its calculated columns are deleted too: <names>.Model AI works with calculated columns under the same rules as the dialogs, and a refusal names the rule.
The cards, approvals and status lines are on Model AI.
A calculated column works like any other column of its BI table:
sum("Margin") (Measures). The measure AI sees the working calculated columns of the model and can use them; a red one is left out.The BI table's preview lists working calculated columns after the warehouse columns, and View SQL on a visual shows the expressions it computes.
"Amount", not "Sales"..if, multiIf, concat, round and the date functions are allowed. today() and now() return the time the query runs.__ at the start, and no double quotes, backticks, backslashes, dots, commas or control characters.These are refused with a sentence that names the rule:
| Not supported | Use instead |
|---|---|
| Columns of other BI tables, or a lookup in a related table | A measure, which reads across the tables joined to the fact table, or the value added to the warehouse table. |
Aggregates such as sum or count, and window functions (OVER) | A measure. |
| Other calculated columns, and measures | Repeat the expression, or use a measure. |
Subqueries, table functions, references to tables, dictionaries, SETTINGS, aliases, * and COLUMNS(. | Name the warehouse columns directly. |
Pipelined SQL (|>) and FROM-first queries, and IN with a list of columns | One SQL expression for the column, comparing values with = and OR. |
Random and other non-repeatable functions, arrayJoin, sleep, throwIf, and user-defined functions | Built-in functions that give the same result for the same row. |
| A list, map, tuple or other composite result | One value per row. |
A calculated column also cannot be:
When you need one of these, add the value to the warehouse table instead, for example as a computed column. It then reaches the BI table as an ordinary warehouse column.
An expression must not fail on one bad row. A function that stops with an error on a value it cannot convert is refused when a form that does not fail exists, and the refusal names that form, for example toInt32 fails on text that is not a valid value, which is not allowed in calculated columns; use toInt32OrNull instead.
| Instead of | Write |
|---|---|
toInt32("Code") or toDate("Day") over text | toInt32OrNull("Code"), toDateOrNull("Day") |
CAST("Code" AS Int32) or "Code"::Int32 over text | accurateCastOrNull("Code", 'Int32') |
toDecimal64("Amount", 2), or a CAST to Decimal, Enum or FixedString | accurateCastOrNull("Amount", 'Decimal(18, 2)') |
intDiv("Total", "Units") or "Total" % "Units" | intDivOrZero("Total", "Units"), moduloOrZero("Total", "Units") |
intDiv("Total", "Units") over fractional values | floor("Total" / |
Text compared with a number or a date, such as "Code" = 5 | Convert the text first: accurateCastOrNull("Code", 'Int64') = 5 |
OrNull forms return an empty value for a row they cannot convert, and the OrZero forms return 0 when the divisor is 0./ between whole numbers or floating-point numbers never fails and is allowed. Dividing Decimal values by a column is refused when a form that does not fail exists.toDate of a date-time column.A calculated column that stops working is never deleted. It turns red instead: the fx badge carries a red dot, and pointing at it shows the reason. The column's properties add a Broken row with the same reason.
While it is red:
calculated column "<name>" of BI table "<table>" is broken; a model editor must fix or delete it.Calculated column "<name>" of BI table "<table>" is broken: <reason>.| Reason | What to do |
|---|---|
column "<X>" is no longer a warehouse column of BI table "<T>" | A column the expression reads was dropped or renamed in the warehouse. Edit the expression, or bring the column back. |
this calculated column was not re-checked after the warehouse changed BI table "<T>"; re-check it | A column it reads changed type or came back, or a clash with a warehouse column went away. DataPrism re-checks it in the background and clears the reason when the check passes; choose Re-check to do it now. |
this calculated column was not re-checked after BI table "<T>" was replaced; re-check it, or the same sentence after a reverted replacement or a deleted BI table | The change could not check the column in time. The background re-check clears it, or choose Re-check. |
a warehouse column of BI table "<U>" now has this name; delete this calculated column to use the warehouse column | A warehouse column with the same name arrived; see below. |
the warehouse view behind BI table "<U>" now has a column with this name; delete this calculated column | The warehouse table behind a BI table now has a column with this name. Delete the calculated column and create it under another name. |
the expression failed against BI table "<T>": <message> | The expression no longer runs against the table, for example after a column changed type. Edit the expression. |
<name>".When the cause is already gone, for example the warehouse column is back, right-click the column and choose Re-check. The item appears only while the column is red. The toast reads "The calculated column works again." when it passes, or names the reason when the column still fails or could not be checked. More on the panel: Check, document and trace a model.
Names are unique in a model, so a warehouse column that arrives with a calculated column's name is a clash. The update from the warehouse never fails because of it:
a warehouse column of BI table "<T>" now has this name; delete this calculated column to use the warehouse column. Delete the calculated column. The warehouse column joins the BI table the next time DataPrism reads the table's columns from the warehouse; the delete does not start that read.Actions that would bring such a name are refused instead:
BI table "<U>" has columns named like calculated columns of this model: <names>.Replacing the table of BI table "<T>" adds columns named like calculated columns: <names>.Reverting the replacement of BI table "<T>" brings back columns named like calculated columns: <names>.. file carries calculated columns with their expressions and records format version 3 when the model has any. On import, a calculated column whose name is taken or whose expression the rules refuse is skipped and listed, and one whose expression fails against its table is created red (Model files and replacing a table).| Limit | Value |
|---|---|
| Expression size | 8,192 bytes. Above it the counter turns red and Preview and Save are disabled. |
| Calculated columns per BI table | 50. One more is refused with BI table "<T>" already has 50 calculated columns, the most a BI table can have. |
| Name | 128 characters |
| Preview | 20 rows, showing up to 10 of the columns the expression reads |
| A visual's query that computes calculated columns | 1,400 KB. Above it the visual answers this query is <n> KB, over the 1,400 KB limit, because of the calculated columns it computes; use fewer calculated columns in this visual or shorten their expressions. |
The other model capacities are on Limits and capacities, and every message on this page is listed in Troubleshooting.
Use a calculated column for a value per row from one BI table that you want to group, slice, filter or sort by. Use a measure for totals, ratios and anything that adds up rows or reads several tables: a calculated column that names a column of another BI table is refused, and the refusal names the table that holds it. Use a warehouse computed column when the value should be stored, or seen by tools outside DataPrism.
The control appears for people with Use DataPrism AI who can edit the semantic model, and it is off while a save runs. Fix with AI appears only beside a refused save, a failed preview or the notice of a red column. Ask an administrator for Use DataPrism AI if you lack it (Who can do what in DataPrism).
The conversion would stop with an error on a value it cannot convert, and a form that does not fail exists. Use the form the message names, such as toInt32OrNull("Code") or accurateCastOrNull("Code", 'Int32'); it returns an empty value for that row instead.
You can change its label, which is what reports show. The label starts as the name; change it with Rename on the column's menu or from the column toolbar (BI table columns). The name is fixed. To use another name, create a new calculated column with the same expression and delete the old one; charts and measures that used the old name stay broken until a column with that name exists again or you point them at the new one.
A column it reads was dropped, renamed, retyped or brought back. After a type change or a return, DataPrism re-checks the column in the background and clears the red state when the check passes. After a drop or a rename, edit the expression. Hover the fx badge to read which applies, or choose Re-check.
A visual that uses no calculated column runs the same query as before. A visual that uses one computes the expression over the rows it reads, so a heavy expression on a large table costs time in those visuals only.
No. Row-level security, relationship keys and the Type and version columns use warehouse columns only, and the editors for them do not list calculated columns. Add the value to the warehouse table if you need it there.
Yes. It previews a draft expression on 20 rows, creates up to 12 calculated columns in one change with their settings, changes expressions and settings, re-checks red columns and deletes columns, under the same rules as the editor. Changing the expression of a column that something uses, and deleting one, wait for your approval unless the mode is Unrestricted, and every change can be reverted. It does not create calculated columns during a Model Builder round from the Warehouse Builder. See Model AI.
On a model with row-level security turned on, a visual that uses a calculated column and fails shows only Query failed - contact an administrator (ClickHouse error code N). Open the column in the model editor and run Preview, which shows the full error.
They return the time the query ran. A visual that is answered from a recent cached result shows the time that result was computed.
The same values as the rest of the model. In a Tokenized PII model, an expression over a PII column computes over the tokenized stand-ins, not the original values (Concepts and glossary).
Names are unique across the whole model: the name may not belong to any column of any BI table, a measure or a BI table, or to a column of a warehouse table behind one of the model's BI tables. It also cannot be a name a row-level security rule names, or a key column of a relationship uses, because those rules and joins find their column by name. The refusal names the holder, for example name "Region" is already a column of BI table "Customers", name "Region" is used by row-level security group "Europe"; pick another name or name "Region Key" is a key column of a relationship of BI table "Sales"; pick another name. Pick another name and use the label for what reports show.