Writing DAX Formulas
Tags: powerbi, dax, dataanalysis
Published: 2020-08-13 10:43:02

## A - Introduction
Every Dax formula, similar to programming variables should be defined/declared by a name and followed by the formula/value.

```js
<Calculation name> = <DAX formula>
```

#### For example:
```js
Order Type = "Allocation"
```
Here, the `Order Type` is the name of the DAX formula and `"Allocation"` is the **value** or `Order Type`.

</br>
</br>

## B - Reminder

As we mentioned [in the introduction](https://dev.to/kevorkkeheian/introduction-5ap8) 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.

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/dh6ncvcxatqq6hc9xy0t.png)

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:

```csharp
Sales Orders = Sales
```
![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/2gif7sa7qva8uksoh1l6.png)

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:
```csharp
Sales Orders = 'Sales Table'
```
or
```csharp
Sales Dates = 'Date'
```


## D - Referring a Column

Referred Columns in a formula should be enclosed within square brackets.
#### example:
```csharp
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:
```csharp
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):
```csharp
Total Sales = SUM(Sales[Amount])
```
```csharp
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:
```csharp
Revenue = SUM(Sales[Amount]) - SUM(Purchase[Amount])
```
or
```csharp
Revenue= [Total Sales] - [Total Purchases]
```
<small>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</small>

**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:
```csharp
Revenue = [Total Sales] - Sales[Total Purchases]
```

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/i7ebfaf4idnjftvtw219.png)

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 = Sales`
- You 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]`