No More DAX? Power BI’s NEW Feature Explained (File Included)

Summary

The video introduces Power BI's new visual calculations feature, simplifying DAX formulas and enabling users to reference fields directly in visuals. It demonstrates creating running totals and moving averages with ease, using templates and DAX functions. The script also covers editing visual calculations, formatting workarounds, and acknowledges current limitations like conditional formatting. The feature is in preview, suggesting improvements are on the horizon, and encourages users to enable it in Power BI Desktop for enhanced data analysis.

Takeaways

• 📊 Visual calculations in Power BI simplify DAX measure creation by allowing direct field references in visuals.
• 🚀 New to Power BI? Visual calculations can reduce the complexity of mastering DAX formulas for beginners.
• 🔗 Follow along with the example file provided in the video description to practice using visual calculations.
• 📉 Previously, creating running totals required complex DAX formulas, but now it's streamlined with visual calculations.
• 🛠️ The 'New Calculation' feature in Power BI opens a pane for creating visual calculations with various templates, including running sums.
• 👀 Visual calculations can be edited directly from the visual pane, making adjustments quick and easy.
• 📈 Visual calculations respond to drill-up and drill-down functionalities, as well as slicers for data filtering.
• 📊 Moving averages, once complicated with DAX, are now easily implemented with visual calculations.
• 📋 Visual calculations are not added to the field list like regular measures, limiting some formatting options.
• 💡 A workaround for number formatting is to use the 'format' function within visual calculations to apply custom formats.
• ⚠️ Visual calculations are a preview feature with some limitations, but improvements are expected over time.
• 💻 To use visual calculations, ensure you have the latest version of Power BI Desktop with the feature enabled in the options.

Q & A

What is the main purpose of visual calculations in Power BI?

The main purpose of visual calculations in Power BI is to simplify the writing of DAX measures by enabling direct reference to fields in visuals, reducing the complexity and allowing users to focus more on the data analysis.

How do visual calculations make creating reports easier in Power BI?

Visual calculations make creating reports easier by providing templates that allow users to quickly apply common calculations without having to write complex DAX formulas.

What is the process of adding a running total to a visual in Power BI using visual calculations?

To add a running total to a visual, select the visual, go to the Home tab, click on 'New Calculation', use the 'fx' button to select the 'Running Sum' template, choose the appropriate field from the list, and insert it into the calculation.

How can users hide or show items in their visuals after applying visual calculations?

Users can hide or show items in their visuals by clicking on the eye icons beside each series in the build pane after applying visual calculations.

How do visual calculations respond to drill-up and drill-down functionality?

Visual calculations respond to drill-up and drill-down functionality just like regular measures, allowing for dynamic data exploration in reports.

What is the process of creating a moving average visual calculation in Power BI?

To create a moving average, go to 'New Calculation', select the 'Moving Average' template, choose the field (e.g., sum of sales), set the window size for the number of periods to average, and name the calculation.

How can users edit visual calculations in Power BI?

Users can edit visual calculations by clicking on the calculation in the visual, selecting 'Edit Calculation', making the necessary changes in the visual calculation pane, and then returning to the report.

What is the recommended course for mastering Power BI and DAX as mentioned in the script?

The recommended course is the Power BI, Power Pivot, and DAX course bundle, which is designed to build skills step by step with practical examples and real-world projects, including personal support from the course creator.

How do visual calculations differ from regular measures in terms of field list visibility?

Visual calculations are not added to the field list like regular measures, so they do not appear in the list of fields and cannot be formatted with number formatting as usual.

What is a workaround for applying number formatting to visual calculations?

The workaround for applying number formatting is to edit the calculation, wrap it in the 'FORMAT' function with the desired format string, and then apply the changes.

What limitations are currently associated with visual calculations in Power BI?

Current limitations of visual calculations include the inability to apply conditional formatting and the lack of appearance in the field list, which also prevents standard number formatting.

How can users ensure they are using the latest version of Power BI Desktop with visual calculations enabled?

Users should go to the 'File' tab, select 'Options and settings', then 'Options', and under 'Preview features', ensure 'Visual Calculations' is enabled. A restart of Power BI is required for the changes to take effect.

Outlines

00:00

📊 Introduction to Power BI's Visual Calculations

This paragraph introduces the concept of visual calculations in Power BI, which simplifies the process of writing DAX measures by allowing direct field references in visuals. It demonstrates how to use visual calculations to create running totals and moving averages without complex formulas, making report creation more efficient. The speaker also mentions an example file for viewers to follow along and highlights the ease of editing visual calculations and their responsiveness to drill-up and drill-down functionalities.

05:04

🔢 Formatting and Limitations of Visual Calculations

The second paragraph delves into the formatting options and limitations of visual calculations in Power BI. It explains how to apply percentage formatting to visual calculations by editing the calculation and using the format function. The paragraph also addresses the inability to apply conditional formatting directly to visual calculations and suggests using the 'show values as' feature as a workaround. The speaker mentions that visual calculations are a preview feature and anticipates future improvements. Finally, instructions are provided on how to enable visual calculations in Power BI Desktop and the potential for Microsoft to expand the feature set.

Keywords

💡DAX formulas

DAX, or Data Analysis Expressions, is a formula language used in Power BI to perform calculations on data. In the context of the video, DAX formulas are presented as complex and potentially overwhelming for users, especially beginners. The script discusses how visual calculations can simplify the process of writing DAX measures, making it more accessible.

💡Power BI

Power BI is a business analytics service by Microsoft that provides interactive visualizations and business intelligence capabilities. The video script introduces a new feature within Power BI called visual calculations, which aims to simplify the creation of reports and data analysis.

💡Visual calculations

Visual calculations is a feature in Power BI that allows users to create DAX measures in a more intuitive and visual manner. The script highlights this feature as a simpler alternative to traditional DAX formulas, enabling users to reference fields directly in their visuals.

💡Running total

A running total is a cumulative sum that increases as new values are added. In the script, the presenter demonstrates how to create a running total of sales by month using visual calculations, which simplifies the process compared to writing complex DAX formulas.

💡Templates

Templates in the context of visual calculations refer to pre-built formulas that users can select to quickly create specific types of calculations. The script mentions the use of templates for running sums and moving averages, showcasing how they can be utilized to streamline the creation of DAX measures.

💡Moving average

A moving average is a technical analysis indicator that smooths out price data by creating a constantly updated average of a specified number of periods. The video script explains how visual calculations simplify the creation of a moving average, which is traditionally a complex calculation.

💡Drill-up and drill-down

Drill-up and drill-down are navigational features in Power BI that allow users to view data at different levels of detail. The script mentions that visual calculations respond to these functionalities, enabling users to interact with their data more effectively.

💡Slicers

Slicers are a type of visual in Power BI that allow users to filter data by selecting one or more items from a list. The script notes that slicers can be used in conjunction with visual calculations, providing an additional layer of data interaction.

💡Conditional formatting

Conditional formatting is a feature that changes the appearance of a cell based on the value it contains. The script points out that visual calculations currently do not support conditional formatting, which is a limitation of the feature.

💡Collapse all

In the context of the script, 'collapse all' refers to a visual calculation function that consolidates data across all categories or groups. The presenter uses this function to calculate the sales as a percentage of the grand total, demonstrating its utility in creating more complex calculations.

💡Format function

The format function in visual calculations is used to apply number formatting to the results of a calculation. The script shows how to use the format function to add a percentage symbol and decimal places to a calculation, which is not possible through standard formatting options due to the nature of visual calculations.

💡Preview feature

A preview feature in software refers to a new capability that is not yet fully released but is available for users to try out and provide feedback on. The script mentions that visual calculations are a preview feature in Power BI, indicating that it is still in development and may have limitations or receive enhancements in the future.

Highlights

Introduction to Power BI's visual calculations as a simpler alternative to complex DAX formulas.

Visual calculations allow direct field referencing in visuals, reducing the need for complex formulas.

Demonstration of creating powerful reports quickly with visual calculations.

Instructions on following along with an example file provided in the video description.

Comparison of traditional running total measures with the new visual calculation method.

Step-by-step guide on adding a running total to a visual using the visual calculation pane.

Explanation of how to hide and unhide items in visuals after applying visual calculations.

Showcasing the drill-up and drill-down functionality with visual calculations.

Tutorial on creating a moving average with visual calculations instead of complex DAX formulas.

Guidance on editing visual calculations and returning to the report.

Promotion of a Power BI course bundle for mastering Power BI and DAX.

Discussion of using pre-built templates for visual calculations and their limitations.

Workaround for applying number formatting to visual calculations.

Explanation of how to calculate percentages of the grand total using visual calculations.

Limitation of conditional formatting not being applicable to visual calculations.

Alternative method of showing values as a percentage of the total using 'show values as' feature.

Instructions on enabling visual calculations in Power BI Desktop's preview features.

Anticipation of improvements to visual calculations as a preview feature.

Invitation to the next video for Excel users to elevate their analysis skills with Power BI.

Transcripts

Ever felt overwhelmed by the complexity  of DAX formulas in Power BI? Or maybe you  

are just getting started and the thought of  mastering DAX feels like a huge challenge.  

What if I told you there's a simpler way to  handle those calculations? Say hello to Power  

BI's brand new visual calculations. Visual  calculations simplify writing DAX measures,  

enabling you to directly reference fields in  your visuals. And that means less hassle and  

more time to focus on what really matters. Let  me show you how you can use visual calculations  

to create powerful reports in no time. By the way,  

you can follow along with the example file  from the link in the video description.

Prior to visual calculations, we had to write  running total measures using multiple DAX  

functions in what would be an overwhelming  formula for a beginner. But now I can simply  

select the visual that I want to add the  running total to. Then on the home tab,  

we've got New Calculation. This opens the visual  calculation pane below the visual. Clicking on  

fx here there are a load of templates we can  use to get started with visual calculations,  

including one for running sum. We can see it takes  one argument, the field, and if we delete it,  

I now have a list of the fields in my visual that  I can work with. There's only one value field,  

that is the sum of sales, and that's the one I  want. So tab to insert it and then press enter.  

And just like that, I've got a running total of  sales by month in my visual. Notice in the build  

pane, we've got these eye icons beside each  of the series. And if I only want to show the  

running sum, I can hide the sum of sales from  my visual. Just note that you can't delete the  

sum of sales though, because this has been used  by the visual calculation, and that's why we now  

have the ability to hide and unhide items in our  visuals. I'm going to leave them both visible, and  

I'll click back to report. And just like regular  measures, the visual calculations also respond  

to the drill-up and drill-down functionality.  Plus, we can use slicers and filter our data.

Let's look at another example. Another  common calculation that is notoriously  

complicated is a moving average. You can see  a typical moving average formula here. It's  

not for the faint-hearted, but it's super  easy with a visual calculation. Let's take  

a look. We'll go into New Calculation and  then clicking on the function dropdown,  

we can choose the moving average template.  You can see it's got two arguments. The field,  

well the field is going to be my sum of sales. And  then the window size is the number of periods I  

want to average. I'm going to average three.  And let's replace the name here with 3 month  

moving average. And press enter. And now I  have the moving average in my visual. Let's  

go back to the report. Now if you ever need  to edit each one of these visual calculations,  

you can click on it and edit calculation.  It takes you back to the visual calculation  

pane. You can make your changes and then  go back to the report when you're done.

Now there are a few limitations which I'll cover  in a moment, but first, as you can see, working  

with DAX formulas in Power BI can be daunting.  If you're finding yourself needing a deeper  

understanding or simply want to master Power  BI, I highly recommend checking out my Power BI,  

Power Pivot, and DAX course bundle. It's designed  to get you up and running with Power BI fast.  

The course builds your skills step by step with  practical examples and real-world projects, and  

includes support from me personally. By the end of  the course, you'll have the confidence to tackle  

any Power BI challenge. So if you're serious about  mastering Power BI and you want to take your data  

analysis to the next level, be sure to check out  the link in the description and pinned comment.

So far, we've used these pre-built templates to  add visual calculations, and these templates use  

a series of new functions that have been built to  support visual calculations. You can see a list  

of them here. When you add a visual calculation  to your model, it's not added to the field list  

like a regular measure, which is why you don't  see it in the list here. And this also means  

you can't apply number formatting as you normally  would. However, I'll show you a workaround. Here  

I have a matrix table with sales by manager  and segment, and I'd like to see sales as a  

percentage of the grand total. So I'm going to add  a new calculation. Now you're not limited to the  

templates available here. You can use regular DAX  functions. Let's give this measure a name. We'll  

call it percent of grand total. And it's going to  divide the sum of sales, and for the denominator,  

I'll use one of the new visual calculation  functions for collapse all. What am I collapsing?  

Well, my sum of sales. And then this function  wants to know how the calculations should traverse  

the matrix. I want to calculate horizontally  across the columns from left to right, continuing  

row by row from top to bottom. So I want  COLUMNS ROWS. Close parentheses and COLLAPSEALL,  

close divide. Press enter. And now I have the  percentage each value is of the grand total.  

But notice it doesn't have the percentage symbol.  And remember, because these measures aren't in my  

list of fields, I can't format them. Instead, I  can edit the calculation. So let's go and do that.  

Edit calculation. And here I'm going to wrap it in  the format function. And then the format goes in  

double quotes: 0.00. I'll have two decimal places  and the percentage sign. And you write these  

formulas much like you'd write an Excel custom  number format. So I'm going to close parentheses  

on format, press enter, and now we have the  formatting. Let's hide the sum of sales. And  

now I just have the percentages in my table. Let's  go back to the report. And there it is. Job done.

However, another limitation of the visual  calculations is you currently can't apply  

conditional formatting to them. If I right-click  on it, you can see the options are quite limited.  

If you want to show values as a percentage  of the total, let me just unhide this matrix  

table. A better option currently is to use show  values as. So right-click on the values field,  

show values as percent of grand total.  Now I have the same percentages. And  

if I right-click here, you can see I also  have conditional formatting. For example,  

we could give them a background color. We'll  use a gradient scale and I'll click okay.  

And now I have some conditional formatting  which I can't currently apply to my visual  

calculations. Visual calculations  are currently a preview feature,  

so I'm sure with time these limitations will be  resolved and more functions will be available.

To try out the visual calculations, make  sure you're running the latest version of  

Power BI Desktop. Go to the file tab down at the  bottom, options and settings, and then options.  

And then under preview features, at the  bottom, you want to make sure Visual  

Calculations is enabled and then click  okay. And then you'll need to restart  

Power BI for them to be available. As Microsoft  continues to improve feature calculations,  

data analysis becomes more accessible  and powerful for everyone. If you're  

an Excel user and you're eager to elevate  your analysis skills, get up and running  

with Power BI with my next video where I guide  you through step by step. I'll see you there.