DAX (Data Analysis Expression) is a library of operations and functions which can be combined to create formulas and expressions within Power BI. This blog provides a compilation of formulas which may be useful in a variety of situations.
DAX Operators
- The following are a selection of DAX operators which may be less obvious. This is not an exhaustive list.
& concatenation function
&& AND function
|| OR function
Splitting Columns Formula
- Given a column of the form [First Name] [Last Name], the following functions can be used to split the names into two separate columns
Last Name = RIGHT(Customers[Customer Name],
LEN(Customers[Customer Name])
FIND(" ", Customers[Customer Name],1,BLANK()))
First Name = LEFT(Customers[Customer Name],
FIND(" ",Customers[Customer Name],1,BLANK()))
IF Highlight Formula
- Highlights specific values within a column (when put on chart legend). In this case the following formula looks for a sub-category field within a categories table and then specifically looks for "Art", "Machines" and "Paper" within that field. The result is a Boolean where the highlighted values are true in the statement
Highlight Sub category =
IF(Categories[Sub-Category] = "Art"
|| Categories[Sub-Category] ="Machines"
|| Categories[Sub-Category] ="Paper" , TRUE, FALSE)
Iterator Formula
- This formula sums up the profit (or any other measure you may be interested in) on a row by row basis using the SUMX function. It has the syntax SUMX(<table>, <expression>). The RELATED function returns a related value from a different table. In this case the 'Product Cost' table which contains information on the unit cost.
Total Profit =
SUMX(Orders
,Orders[Sales]-Orders[Quantity]*
RELATED('Product Cost'[Unit Cost] ))
Dynamic Cross-Filter
- The following formula in this case counts the number of distinct sub-categories but allows filters between two tables to run in both directions on a temporary basis by using the CROSSFILTER function. If one were to look at the data model they would find that the relationship between the Orders and Product Cost table would filter in one direction only.
- The CALCULATE function is immensely useful and deserves its own blog post but in simple terms it can be used to change the filter context. It has the syntax CALCULATE(<expression>, <filter1>, <filter2>, ...)
Dynamic Cross Filter =
CALCULATE(
DISTINCTCOUNT(Categories[Sub-Category]),
CROSSFILTER(
'Orders'[Product ID], 'Product Cost'[Product ID], Both)
)
Total Sales Previous Year
- This formula speaks for itself. In this case it shifts the dates column back by one year and then calculates the total sales at this new shifted point in time. This makes it useful for comparing previous years with the current year for a particular metric. Note that a date table is often required when using this function.
Total Sales Same Period Last Year =
CALCULATE([Total Sales],SAMEPERIODLASTYEAR(_date[Date]))
Parallel Period Function
- Similar to the SAMEPERIODLASTYEAR function but shifts the dates backwards or forwards by specified number of intervals. In this case the formula calculates total sales one month in the past. Note that a date table is often required when using this function
Total Sales Parallel Period =
CALCULATE([Total Sales] ,PARALLELPERIOD(_date[Date],-1,MONTH))
Dynamic Symbols Formula
- Another very versatile formula which has many possibilities. This is useful if for given values of a metric you want to display specific symbols or format numbers in a specific way, i.e. 70 000 is formatted as 70K etc. Usually multiple IF statements are used.
- As this is a formula designed to change the formatting of a metric you need to write these in a DAX format window. This can be done by making a normal measure in DAX and then changing the format to dynamic under the 'Measure Tools' tab. The word 'Format' should appear next to the space in which you write the DAX formula.
IF([Total Profit]>1000, "$#,.0K",
IF(AND([Total Profit]<999, [Total Profit]>-999), "😢;😢" ,
IF([Total Profit]<-1000, "🤬;🤬"
)))
- This formula is basically saying if the total profit is less than -1000 then display 🤬, if its between -999 and 999 then display 😢, and if its above 1000 then do the following:
'$' display the dollar symbol
'#' only show digit if it exists
',' add a thousand separator
'.0' give value to only 1 decimal place
'K' add the suffix K
Closing Remarks
- Hopefully this blog has given some useful DAX formula that may be useful in certain situations. There are certainly many more to go through which is a topic for a future blog.