Power BI has been my go-to data visualisation and business intelligence tool for the last 3 years. I have built corporate dashboards with it and used it as my own little exploration tool when I'm tackling a new data set for the first time. Since I've been getting more and more proficient at using the tool, there are some functions that I go back to time and time again. I had a look through the reports and analyses that I conducted in 2020 and picked out my most used DAX functions - I found 12 functions in total. Read on to learn more about them. The below functions are mostly in order of usage with the first category being the most used and the last category being the least used. All generalised syntax below has been taken from the official . Power BI reference documentation 1. BASIC SUMMARY FUNCTIONS This is the only category of functions I have in this list containing 7 of the basic summary functions. These are probably going to be the first functions anybody learns when just getting started with Power BI and they are definitely going to make the list for the most common functions used by just about anybody. I would usually use any one of these functions together with the other functions coming up next. In Power BI, it isn't always necessary to create measures just to perform a simple SUM or an AVERAGE, for example because these operations can be performed within each chart or table without needing to go through the extra effort of creating a measure. The only time I might use these functions on their own is to give the measure a more meaningful name and this allows me to: Preserve the original name of the column Not have to rename the chart field or table column each time I add the column to a chart/table Get around the (often ugly-looking) automatic naming of fields when applying an aggregation to a field/column in a chart/table COUNT & DISTINCTCOUNT The COUNT function counts the number of non-blank cells in a column. COUNT (<column>) DISTINCTCOUNT (<column>) And obviously DISTINCTCOUNT just counts the number of unique values. SUM & AVERAGE These functions allow you to SUM or AVERAGE the values in a column SUM( <column>) ) AVERAGE( < > column MIN & MAX These functions return the MIN or MAX values of a column (< >) MIN column (< >) MAX column 2. SUMMARIZE Alright, now we get to the somewhat more interesting functions. I probably use the SUMMARIZE function the most and is my favourite (if I can actually pick a favourite). Unlike the other functions in this list (except for LASTDATE at the end), the SUMMARIZE function returns a table and I typically use it so that I can further apply aggregations over different groupings. For example, suppose my main table is highly transactional and I need to roll it up a bit so that I can create new functions that aggregate on the new rolled up table. The SUMMARIZE function will take in grouping variables and named expressions and return a new table from it. You can also join on these tables and create relationships just as you would on a normal table. Pretty nifty if you ask me. SUMMARIZE ( , [, ]…[, , ]…) < > table < > groupBy_columnName < > groupBy_columnName < > name < > expression Here, square brackets indicate optional arguments and the portion simply implies that more arguments can be added. ... 3. CALCULATE The CALCULATE function performs an aggregation along with one or more filters. When you specify more than one filter, the function will perform the calculation where all filters are true. The general syntax is as follows: CALCULATE(< >[, < > [, < > [, …]]]) expression filter1 filter2 The CALCULATE function can be useful to you force an aggregation to completely ignore some or all report-based filters. However, in the reports I have designed, I have only used this a handful of times and only for very specific use cases. 4. IF Ah, yes. The great and powerful IF function. I use the IF function A LOT. But mostly when creating a new flag or classification column and then further combining it with the other calculation functions listed here. Also feel free to nest the IF function within other IF functions as much as you like. IF(<logical test>, < true>[, < alse>]) _ value _ if _ value _ if _f 5. IFERROR Very similar to the IF function above but specifically looks for any errors and returns an alternative result. This function is a life saver when trying to catch errors and display them in a more aesthetically pleasing way. Also works well with the BLANK function which returns a blank value that is readable by Power BI (because using empty quotes like " " may not be regarded as a blank and is important to note when you are filtering tables/charts). IFERROR( , error) value value _ if _ 6. ISBLANK Used a lot in conjunction with IF to create flags that highlight blanks or to exclude them altogether. ISBLANK (<value>) 7. WEEKDAY I use this function to create a week date or week date in my date table. The return_type argument is very important because it determines what day of the week the week begins on. start end WEEKDAY(< >, < >) date return_type These are the formulas for week date and week date: start end Week = - ( , ) + = - ( , ) + Start Date date WEEKDAY date 2 1 Week End Date date WEEKDAY date 2 7 This formula for week date is the most versatile I have ever used and handles new years perfectly. start 8. FORMAT This function can be used to convert columns to any format. However, I use this function to create a month-year or period column from the date column in my date table. FORMAT(< >, < >) value format_string This is the formula for creating a month year column: MonthYear = ( , ) FORMAT date "MMM YYYY" Using the FORMAT function in this way will force the resulting MonthYear column to be converted to text. However, there is a little trick for displaying this as text but allowing the correct month sorting (rather than alphabetical sorting). Leave a comment below if you'd like me to go over this little hack. There are many use cases for when you want to force Power BI to display dates in a very specific way rather than allowing it to decide how best to present your dates so don't discount the power of using this function in this way. 9. LOOKUPVALUE If you are familiar with VLOOKUP in Excel then you will be comfortable with LOOKUPVALUE too. This function allows you obtain a single column from another table by creating a link that exists in this function only rather than having to completely link the two tables together. However, as stated in the : Power BI documentation If there is a relationship between the result and search tables, in most cases, using RELATED function instead of LOOKUPVALUE is more efficient and provides better performance. LOOKUPVALUE( , , [, , ]… [, ] ) < > result_columnName < > search_columnName < > search_value < > search2_columnName < > search2_value < > alternateResult 10. EOMONTH Used to get the last day of the month of a given date for as many months in the past or the future. This function can be useful when used together with the CALCULATE function as a filter argument. EOMONTH(< >, < >) start_date months 11. DATEDIFF Used to get the difference between 2 dates in days, months, quarters, years, etc. DATEDIFF(< >, < >, < >) start_date end_date interval You know, even after using this function so many times I always forget which way the calculation is performed and I have to try both ways. Is it start_date - end_date end_date - start_date? or is it 42? 12. LASTDATE This function returns the last date in a column. This function is a little sneaky in that it actually returns a table with one column and one row and so you can use it in any function that requires a table argument. LASTDATE (<dates>) I hope you find this list of functions useful to you. Once you learn a couple of the basic functions in Power BI and then expand to include a few common functions you can solve almost any problem and design powerful reports and dashboards! Happy visualising!
