No More DAX? Power BI’s NEW Feature Explained (File Included)
Summary
TLDRThe 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
📊 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.
🔢 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.
Mindmap
Keywords
💡DAX formulas
💡Power BI
💡Visual calculations
💡Running total
💡Templates
💡Moving average
💡Drill-up and drill-down
💡Slicers
💡Conditional formatting
💡Collapse all
💡Format function
💡Preview feature
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.
5.0 / 5 (0 votes)