13. DAX: Data Analysis Expressions
13.4 DAX Syntax
Measure Name = FUNCTION( Table[Column], argument2, ... )
- Measure/column name, then
=, then the expression. - Table names with spaces or reserved words need single quotes:
'Date'[Year]. (We always quote'Date'because Date is also a function name.) - Columns are always in square brackets:
Orders[Quantity]. - Text strings in double quotes:
"Blinkit". - Comments:
--or//for one line,/* … */for multiple lines. - Use Shift + Enter for a new line in the formula bar; indent for readability.
- DAX is not case-sensitive for function names, but consistent style helps.
Data types in DAX
Whole number (Int64), Decimal number, Currency (fixed decimal), Date/time, Boolean (TRUE/FALSE), String, and BLANK (DAX's version of null). Most arithmetic treats BLANK as 0, while visuals hide rows where all measures are blank.
Operators
| Type | Operator | Example |
|---|---|---|
| Arithmetic | + - * / ^ |
Orders[Quantity] * RELATED(Product[Unit Price]) |
| Comparison | = == <> > >= < <= |
Orders[Delivery Time Mins] <= 10 |
| Text concatenation | & |
DarkStore[Area] & ", " & DarkStore[City] |
| Logical | && (AND), || (OR), NOT() |
Orders[Platform] = "Blinkit" && Orders[Quantity] > 5 |
| List membership | IN { } |
DarkStore[City] IN { "Pune", "Nashik" } |
About this data: This table uses columns: Type, Operator, Example. Values shown are the lesson examples already on this page (not a new dataset).
= treats BLANK as equal to 0 or empty string; == is a strict equality where BLANK only equals BLANK.
Ravindra Bagale's Tip
A common syntax mistake is referring to a measure with a table prefix (Orders[Total Sales]) or to a column without one. Use Table[Column] for columns and [Measure] for measures. It makes formulas readable and avoids ambiguity. Clear?
Ravindra Bagale's Tip – मराठी
Syntax मधली एक common चूक म्हणजे measure ला table prefix लावून (Orders[Total Sales]) किंवा column ला prefix शिवाय refer करणं. Columns साठी Table[Column] आणि measures साठी [Measure] वापरा. त्यामुळे formulas वाचायला सोपे होतात आणि संदिग्धता टळते. समजलं का?
Ravindra Bagale's Tip – हिंदी
Syntax की एक common गलती है measure को table prefix के साथ (Orders[Total Sales]) या column को बिना prefix के refer करना. Columns के लिए Table[Column] और measures के लिए [Measure] इस्तेमाल करो. इससे formulas पढ़ने में आसान होते हैं और उलझन नहीं होती. समझ आया?
Loading DAX practice tasks...
- Name = formula (Easy): Write a measure called Cups Sold that adds Sales[Cups].
- Table[Column] reference (Easy): Write Avg Price = the average of the Price column in Sales.
- Text needs double quotes (Easy): Write Latte Cups = cups sold where Item is "Latte".
- Commas separate inputs (Easy): Write Revenue = SUMX over Sales of Cups × Price.
- Referencing a measure (Medium): The model already has a measure [Revenue]. Write Revenue London = [Revenue] for City "London" only.
- Comments do not change the answer (Medium): Write Card Sales = number of Sales rows where PayMode is "Card". Add a comment after // at the end of the line.
- Table names with spaces (Medium): Write Shops = COUNTROWS of the Shop table, writing the table name in single quotes.
- Operators: & joins text (Hard): Write Top Item Label = the text "Item: " joined with the Item that has the most cups. Use TOPN to keep the top item by cups and CONCATENATEX to turn it into text.
- Nested functions (Hard): Write Avg Revenue per Shop = the average of [Revenue] across the list of shop names.
- Scotland card revenue (Hard): Write Scotland Card Rev = [Revenue] where Shop Country is "Scotland" and PayMode is "Card".
Revision card