DAX is a formula expression language which is called (DAX) which can be used with various visualization tools like Power BI. It is also known as a functional language, where the full code is kept inside a function. DAX programming formulas contain two data types: Numeric and Other.
Each is linked to the other by having common columns. Here is a simple diagram of Power BI Dashboard Example showing the relationships.
Calculated Columns
Calculated column allows you to create new columns based on the given data.
For example, there is no ‘ Final price’ column available in the Items table. Here, the DAX function is used to calculate a new column when only total price & quantity are available.
Price = List_Items[MRP]*List_Items[Qty]
In the data shown in above Power BI example, each row will now have the respective calculated price.
Measures
You can perform a calculation using measure without the need to add any data as shown in the below Power BI example. This is very helpful for reports where the price can be displayed, without requiring an entirely new column to store it.
Example:
1] Total of the MRP column * Total of Qty column
Tables
DAX functions in tabular model return entire tables. For example, In order to generate a list of all the country the organization has clients in, use the function:
cities touched = DISTINCT(Customers[City])
A word on Filters
Filters hide rows that don’t fit given criteria. A calculation after filtering out data will be applicable only on a row which matches those criteria.
Power BI DAX Functions
Some Important DAX functions are:
Average
This DAX function allows you to find the average from a given set of values as shown in the below Power BI example.
Example –
AvgComm = AVERAGE(List_Items[Price])
Max
Helps you to find the maximum from a given set of values.
Example – Find out the highest order.
HighSale = MAX(List_Items[Price])
Min
Helps you to find the minimum set of values.
Example – Allows you to find out the lowest order.
LowestSale = MIN(List_Items[Price])
Count
Count any umerical data.
Example – Count number of ticket issued.
TicketVolume = COUNT(Invoices[Ticket])
Concatenate
This function helps you to join values in calculated columns. You can use ConcatenateX if using in measures.
Example – Concatenate the Item names, and MRPs will give a unique code for all the price points at which each product is sold.
ProMrp = CONCATENATE(List_Items[Item],List_Items[MRP])
TotalYTD
The function allows you to calculate the sum from the start of the current Year to the specified date. It performs calculate base on a calendar year, not a financial year.
Example – Calculate Sales totals for the price column.
CumiSales = TOTALYTD(SUM(List_Items[Price]),Invoices[Date])
All
Returns everything. Ignores filters.
Example – Used with the calculate function above.
