What is DAX? | Power BI
DAX is PowerBI analytical expression language query and transforms PowerBI Datasets.
DAX (Data Analysis Expression) was initially created to transform data in Power Pivot and for querying Analysis Services tabular model. DAX is a library of “functions and operators” which are combined to create formulas and expressions.
DAX is a functional language that is made up of expressions that are predefined function calls. There are no loops and jumps in DAX. If you are familiar with writing excel formulas then learning DAX is very easy.
= if(B2 < 0, "On time", "Arrear")
DAX snippet above has a conditional statement that compares the values in cell B2 and places it into one of the two buckets either On-time or Arrears.
Comparing to Excel where every formula takes cell reference like (C2, D6) or cell range(C2: H45) as input, whereas in DAX we pass column names of a table to the functions. DAX has a concept of context, every function in DAX works in either Filter context (i.e apply the formula to all cells in the column) and Query context to apply calculation only on a particular set of cells in a column.
Example DAX Code:
paymentStatus = if(payments[dueDays] < 0 ,"On time","Arrear")
Above DAX code has input payments[dueDays] inside of a conditional statement which refers to column dueDays of the table payments for grouping the data. Comparing the two codes we see that both the formulas are almost the same.
Types of Calculations in Power BI
Calculated Columns
Calculated columns are derived columns based on existing columns in the source table as discussed above. The main thing to consider while using calculated columns is scalar functions, which are functions whose output is a single value, which cannot be used to create calculated columns. Also, consider moving frequently used calculations to calculated columns as they are available beforehand and need not be computed on the fly when a filter or page is refreshed in the report.
Measures
Another place where DAX is used is in measures. Measures are aggregated on top of the Power BI table. For instance, to calculate the total order amount in a table we create a measure like the one below.
OrderAmt = SUM(payments[orderAmt])These Measures can be used in values of any visuals inside of reports and the latest version of Power BI also support them in filters. Simple DAX written inside a measure is fired every time when a cross-filtering or navigating through visuals is performed. Microsoft recommends limiting the use of measures in a single page to lighten the load of the DAX query engine for faster report refreshes. A Lot of complex calculations can be performed using DAX.
Now I hope you got an idea of what DAX is, Writing Dax is not that difficult but a little bit tricky.
Writing DAX
My experience with DAX initially was mostly correlating it with Excel and SQL. Commonly used formulas to perform aggregations are sum, count, avg, and mean.
Consider the following simple formula.
OrdersPlaced = COUNT(Orders[OrderID])For example, say you need to count the number of orders from a particular vendor, we need to use Calculate function, Calculate is used to add filters to the aggregation evaluation. Filtering the table records based on a condition can be done as shown below.
OrdersPlacedHp = calculate(COUNT(Orders[OrderID]), Orders[Vendor] ="Hp")Another way of filtering the table is by making use of iterative aggregate functions like SUMX, COUNTX. This function operates in Query Context. An example will make it clear, Consider a table with the following data
Imagine you want to calculate total marks of all the sections across all the subjects, This can be done using DAX given below
TotalMarks = SUM(Grades[TotalMarks])Simple right, Now Imagine you have to get total marks scored in Maths across all sections 🤔 Hmmm, How can we do that?
TotalMathMarks = SUMX(FILTER(Grades,Grades[Subject]=="Maths"),TotalMarks])Well, observe the above formula we have a filter expression to return the TotalMarks of rows which contains Maths value in Subject and sum them up.
So the difference is filter context will work on all the rows of a table and QueryContext will be applied only on a subset of rows.
All the function names ending with X work on data at Query context.
OrderPlacedHp = COUNTX(filter(Ords,Ords[Vendor]="Hp"),Ords[OrderID]))Using iterative functions like earlier or earliest (which compares the current row with other records in the table) on larger datasets is not recommended as it needs to iterate over the whole table and no comparisons might work and even crash the system. A similar way of writing measures can be applied to other aggregate functions like Sum or Avg.
There are few functions that return Table as an output, which are pretty useful if you need to perform some calculations. The most used Table functions are Distinct or Values.
UniqueOrders = Distinct(Orders[OrderID])These table Dax functions return unique values of a column which can be useful in building relationships between two tables with no Primary key but needs to be joined. Okay, don’t be worried about what relationships are why do we need to create one. we’ll be explaining relationships and Table functions in detail in the upcoming blog.
To learn more about DAX here are few resources below to get started.
Microsoft Power BI Documentation: https://docs.microsoft.com/en-us/power-bi/guided-learning/introductiontodax?tutorial-step=1
Definitive Guide to DAX: https://www.amazon.in/Definitive-Guide-DAX-intelligence-Microsoft/dp/073569835X
There are some great blogs and communities which can help u in solving if you are stuck writing DAX Queries or feel free to reach out to me via mail
Power BI Community Blog: https://community.powerbi.com/t5/Desktop/DAX-Forum/td-p/13661
