A - Introduction
Every Dax formula, similar to programming variables should be defined/declared by a name and followed by the formula/value.
<Calculation name> = <DAX formula>
For example:
Order Type = "Allocation"
Here, the
Order Type
is the name of the DAX formula and
"Allocation"
is the value or
Order Type
.
B - Reminder
As we mentioned in the introduction that each DAX formula, whether it is a calculated column, calculated table or a measure returns a value. In other words “Allocation” is the returned value.
This DAX formula is considered as a measure. So, whenever we drag this measure into a values field, it will return the value “Allocated” to each field of the visual.
So the returned result of a DAX will be
"Allocation"
for each row.
C - Referring a Table
With DAX you can duplicate a table or a column using the same method as the above, but instead of a text inside a quotation you use the name of the table.
For Example:
Let’s suppose that you have a table called
Sales
in your model. And you want to duplicate the table using a Calculated Table you will use the below formula:
Sales Orders = Sales
Here the value or Sales Order which is the
Sales
table that was already in the model does not need single quotes.
Single quotes are needed when the value is a reserved word such as
Date
or the table name does not include embedded spaces.
EX:
Sales Orders = 'Sales Table'
or
Sales Dates = 'Date'
D - Referring a Column
Referred Columns in a formula should be enclosed within square brackets.
example:
Total Sales = SUM([Amount])
In the above example the value in the
SUM
function is the column name.
The column names are unique in the table. Some references tell you that you can use the column without preceding the table name.
But, to improve the readability of your formulas, it is preferable that the column reference precedes with its table name.
example:
Total Sales = SUM(Sales[Amount])
Another reason for preceding the table name in a column reference formula is that you never know when you will update your model. As we mentioned earlier, the column name is always unique in a table, but can be found multiple times in a model which includes multiple tables.
E - Referring a Measure:
Finally, you can refer a measure in your formula. Let’s suppose that we have the below column referring formulas (they are called measures):
Total Sales = SUM(Sales[Amount])
Total Purchases = SUM(Purchases[Amount])
And you want to calculate the difference between
Sales amount
and
Purchase Amount
.
To do this there are multiple ways:
Revenue = SUM(Sales[Amount]) - SUM(Purchase[Amount])
or
Revenue= [Total Sales] - [Total Purchases]
Note: This is not the right way to calculate the profit, but just an example to show how to refer to a measure in DAX
Note: Measures are a model-level object.
Although it is not recommended, but you can also precede a measure reference in a DAX formula with its table name.
example:
Revenue = [Total Sales] - Sales[Total Purchases]
As we mentioned earlier many reasons to precede the table name in column referring measure, another reason is to differentiate between column referring and measure referring formulas.
Summary:
You can refer a TABLE in a DAX Formula
Sales Orders = SalesYou can refer a COLUMN in a DAX Formula
Total Sales = SUM([Amount])You can refer a MEASURE in a DAX Formula
Revenue = [Total Sales] - [Total Purchases]