Power BI Formula Syntax, Comments, And Variables

No comments

Today, we are diving into what you could do in the Power BI formula bar in terms of syntax, comments, and variables.

These are all very important techniques that you could use in your work patterns. But before we jump into Power BI formulas, I’ll show you how I sort my Dates column within my visuals.

Simplifying Presentation Of Dates

The first thing that I will do is to bring in the Dates table.

Showing the Date table - Power BI Syntax

One thing that can be annoying with the Dates table is that it builds an automatic hierarchy into your table.

What I do is click on the drop-down menu and choose Date instead of Date Hierarchy.

Removing the Date Hierarchy - Power BI Syntax

After that, you will have every single date in just one column.

Result of removing the Date Hierarchy - Power BI Syntax

One other thing that I am going to do is change the date format by going to the Data area.

I will then highlight the whole Date column and select one of the other available date formats below.

Choosing a new Date format - Power BI Syntax

After that, we can come back to see our Date column in its new format.

Displaying the new date format - Power BI Syntax

I’ll drag in the Date table again and remove the hierarchy one more time.

Then, I can make a slicer out of it so that I can quickly change the timeframe to look at a specific range of time.

Creating a slicer for the date - Power BI Syntax

Now that I have my date slicer, I’ll show you some Power BI formulas.

Power BI Formula Using FILTER Function

Let’s create a formula that is more complex and write it in a needlessly complicated way so that we can simplify it later on.

What if we wanted to get a far more specific result? For example, I just want to look at Florida Sales within my measure so I can compare my overall sales to just my sales in Florida.

To do this, we have to create a new measure called Florida Sales and use a function called CALCULATE which we will go over more later.

Inside it, we can put our Total Sales measure since you can actually put measures inside of formulas through measure branching, which is a concept unique to Power BI.

Creating Florida Sales measure

And then, I will use the function called FILTER and look through the Locations table. I could then use the State Code and equate it to “FL” in order to just look at Florida.

Basically, FILTER allows me to create a filter within a formula.

Filtering Locations via State Code

So just press Enter after typing in the formula, and then you can drag in the Florida Sales measure to compare it to our Total Sales measure.

From here, you can calculate more interesting things like the percentage of Florida Sales to Total Sales.

Total Sales vs Florida Sales

But I want to do something even more complicated here. I do not want to just look at Florida Sales, but also at sales of specific products.

To do this, we will use the FILTER function again and put in the Product Name from the Products table and set it equal to “Product 1”.

Filtering Products via Product Name

And then, I am going to use the operator OR and put in the Product Name equal to “Product 2”. Finally, I will rename the measure to Florida Sales of Product 1 or 2.

This means that I am filtering the results to look at the specific sales in Florida with the names Product 1 or Product 2.

Specific sales full measure

To get some results in, I will change the formula to Product 2 and Product 5, rename the measure accordingly, and then widen the timeframe by using the date slicer.

Specific Florida Sales sample output

The one thing that I want to highlight here is how the formula looks too long and complicated.

We can clean things up by pressing Shift+Enter to bring a line down one row and then use the Tab key to create an indent.

We bring down CALCULATE and the two FILTER functions. We create indents before the FILTER functions.

You can see immediately how much easier it is to read the formula compared to what we had earlier.

Formula with proper spacing

This is a really easy fix for when you need to write complex formulas.

The way I usually do it is if there is a function that has logic in it, I will usually place it on a row of its own with an indent in front of it so that the functions would be displayed in a descending manner.

Power BI Formula Using Variables

Another way of simplifying a Power BI formula is through variables.

What you can do here is write in VAR and then name your variable as long as it is one word with no spaces.

For our example, we will use the name FloridaLocation and then set it equal to the FILTER function that takes Florida Sales. Although you can store singular results inside variables, you can also put tables in it.

Creating FloridaLocation variable

To make this work, you need to put RETURN before your formula’s final logic.

Then, you can put the variable you just created to replace the whole function.

Using FloridaLocation variable inside CALCULATE

I can create another variable called ProductSelection, set it equal to the other FILTER function, and use it as a replacement for the function down there again.

Now we can see how much cleaner it is instead of the function that we had before.

Creating and using FloridaSelection variable

Using Comments In The Power BI Formula Bar

The last thing that I want to go over is writing comments in your formula bar. This can help your team or users understand your Power BI formula.

Just by typing in forward slash twice (//), you can say something like “this measure calculates Florida sales for different products”.

Creating comment at the end of the formula bar

You can put comments wherever you like since they do not actually get registered as calculations in your formula bar.

Example of a comment at the end of the line

Conclusion

In this blog, I have discussed how to sort out your Power BI formula to keep things simple.

The one thing that I do not want to see is writing formulas out without any thought on how to make them more readable. Because when you get more complex and advanced with DAX, the formulas tend to become unreadable and incomprehensible.

You will do yourself a huge favor by setting up your formulas efficiently.

All the best,

Sam

Membership Banne
Center of Excellence

***** Related Links *****
Formatting DAX Code
Using Variables In Power BI – A Detailed Example
Effective Ways To Use The GROUPBY Function In Power BI

***** Related Course Modules *****
Ultimate Beginners Guide to DAX
Mastering DAX Calculations
DAX Formula Deep Dives

***** Related Support Forum Posts *****
Grouping And Banding In Power BI – Many Criteria – DAX Patterns
Custom Date Formats For Power BI
Create DAX Table Conditionally
For more syntax queries to review see here…..

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.