My Most Used Power BI Functions of 2020

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 17 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>, <value_if_true>[, <value_if_false>])

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(value, value_if_error)

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 start date or week end date in my date table. The return_type argument is very important because it determines what day of the week the week begins on.

WEEKDAY(<date>, <return_type>)

This is the week start date formula:

Week Start Date = date - WEEKDAY(date, 2) + 1
Week End Date = date - WEEKDAY(date, 2) + 7

This formula for week start date is the most versatile I have ever used and handles new years perfectly.

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 or is it end_date - start_date?

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!

Follow me on Twitter for my latest data science content.