📘 Number Functions
SUM()
SUM([Sales])
What it does: Adds all values of a numeric field across rows.
Example: 100 + 200 + 300 = 600
ROUND()
ROUND([Profit]/[Sales], 2)
What it does: Rounds to a specific number of decimal places.
Example: 0.416 → 0.42
CEILING()
CEILING([Sales]/1000)
What it does: Rounds number up to the nearest integer.
Example: 50.2 → 51
POWER()
POWER([Quantity], 2)
What it does: Raises a number to a power.
Example: 3^2 = 9
ABS()
ABS([Profit])
What it does: Converts negative numbers to positive.
Example: ABS(-500) → 500
📙 String Functions
CONCAT()
[Customer] + ” from ” + [Region]
What it does: Joins two or more strings together.
Example: “Priya” + ” from West” → Priya from West
LEFT()
LEFT([Product], 3)
What it does: Extracts characters from the left of a string.
Example: Laptop → Lap
FIND()
FIND([Product], “top”)
What it does: Finds the starting position of a substring.
Example: “Laptop” → 4
UPPER()
UPPER([Region])
What it does: Converts text to uppercase.
Example: west → WEST
TRIM()
TRIM([Customer])
What it does: Removes extra spaces from start/end of text.
Example: ” Tableau ” → “Tableau”
🧠 Logical Functions
IF…THEN
IF [Profit] > 0 THEN “Profit” ELSE “Loss” END
What it does: Returns a value based on a condition.
Example: Profit = -1000 → Loss
IIF()
IIF([Sales] > 30000, “High”, “Low”)
What it does: Inline IF function that returns TRUE/FALSE output.
Example: Sales = 32000 → High
ISNULL()
ISNULL([Discount])
What it does: Checks if value is NULL.
Example: Discount = NULL → TRUE
AND / OR
[Profit] > 0 AND [Sales] > 5000
What it does: Combines two or more conditions.
Example: TRUE AND FALSE → FALSE
CASE
CASE [Region] WHEN “East” THEN “Zone A” ELSE “Other” END
What it does: Evaluates multiple conditions like a switch-case.
Example: Region = East → Zone A
📆 Date Functions
TODAY()
TODAY()
What it does: Returns current date.
Example: TODAY() = 2025-06-27
DATEDIFF()
DATEDIFF(‘day’, [Order Date], TODAY())
What it does: Returns difference between two dates.
Example: 2025-06-20 → 7 days
DATEADD()
DATEADD(‘month’, 1, [Order Date])
What it does: Adds units to date.
Example: 2025-06-01 + 1 month → 2025-07-01
YEAR()
YEAR([Order Date])
What it does: Extracts year from date.
Example: 2025-06-27 → 2025
DATETRUNC()
DATETRUNC(‘month’, [Order Date])
What it does: Truncates date to first of month.
Example: 2025-06-15 → 2025-06-01
🔎 LOD Expressions
FIXED
{FIXED [Customer] : SUM([Sales])}
What it does: Calculates at fixed granularity regardless of filters.
Example: Fixed sales for each customer
INCLUDE
{INCLUDE [Product] : AVG([Profit])}
What it does: Adds detail to view level aggregation.
Example: Include Product → AVG by Product
EXCLUDE
{EXCLUDE [Region] : SUM([Sales])}
What it does: Ignores dimension from current view.
Example: Exclude Region → National sales total
📊 Table Calculations
RUNNING_SUM()
RUNNING_SUM([Sales])
What it does: Calculates cumulative running total.
Example: 100, 200, 300 → 100, 300, 600
RANK()
RANK([Profit])
What it does: Ranks data based on value.
Example: 600, 400, 200 → 1, 2, 3
LOOKUP()
LOOKUP([Sales], -1)
What it does: Returns value from previous row.
Example: Today’s sales compared to yesterday
INDEX()
INDEX()
What it does: Returns row position in partition.
Example: First row = 1, second = 2
