xmetricFunctions

Tableau function Operators

Tableau ^ (power) to SQL

[a] ^ 2 raises a to the power 2, like power(a, 2). In Tableau's precedence the minus sign binds tighter than ^, so -[a] ^ 2 is (-a)^2, always positive. DuckDB's -a ^ 2 agrees; rewriting it as -power(a, 2) flips the sign.

Updated · Checked on the xmetric engine

What is the syntax?

a ^ b
a, b
numbers, numeric fields or aggregates.

Returns a number: a raised to the power b. Null if either side is null.

What does it return, and what is the SQL?

Each example runs on the same 8 sample rows twice: the Tableau formula on the xmetric engine, and the SQL in DuckDB.

What is a squared?

Tableau

[a] ^ 2

SQL (DuckDB)

SELECT power(a, 2) AS result
FROM input
ORDER BY id

Row 2: (-7)^2 is 49.

aResult
749
−749
24
981
525
null
11
−39

The Tableau formula and the SQL return the same 8 rows.

Where does a straight translation go wrong?

Common mistake

Why does -power(a, 2) come out negative?

Tableau applies the minus before ^, so -[a] ^ 2 squares -a. -power(a, 2) squares first and negates after. Move the minus inside: power(-a, 2).

Tableau

-[a] ^ 2

Tempting SQL

SELECT - power(a, 2) AS result
FROM input
ORDER BY id

SQL that matches

SELECT power(- a, 2) AS result
FROM input
ORDER BY id
aTableauTempting SQLSQL that matches
749−4949
−749−4949
24−44
981−8181
525−2525
nullnullnull
11−11
−39−99

The SQL that matches returns the Tableau result on every row; the marked cells are where the tempting SQL differs.

How do I write it in Snowflake or BigQuery?

The first example, written for each warehouse from its documentation. These are not run here; DuckDB is.

Snowflake

SELECT POWER(a, 2) AS result
FROM input
ORDER BY id

Snowflake documentation

BigQuery

SELECT POWER(a, 2) AS result
FROM input
ORDER BY id

BigQuery documentation

What does your own ^ formula return?

Edit the formula and run it on the 8 sample rows. Fields: [id], [a], [b], [x], [p], [q], [s], [t], [d], [e].

Runs in your browser on the xmetric engine. Nothing is uploaded.

Which functions go with it?

What do people ask about ^?

Is ^ the same as POWER?

[a] ^ [b] and POWER([a], [b]) return the same value; only the precedence of a leading minus differs.

Which way does 2 ^ 3 ^ 2 group?

Left to right: (2 ^ 3) ^ 2 = 64. Add parentheses when you mean 2 ^ 9.

Can I use ^ in Snowflake or BigQuery?

No. Use POWER; in BigQuery ^ is bitwise XOR.

Where is this documented?