Save content
Have you found this content useful? Use the button above to save it to your profile.
ABC of Power BI: ‘L’ is for lifetime learning | accountingweb
iStock_mf3d_learning

ABC of Power BI: ‘L’ is for lifetime learning

by

Continuing his ABC of Power BI series, Hugh Johnson shares 10 great (mostly free) resources to help you on your Power BI journey.

5th Oct 2022
Save content
Have you found this content useful? Use the button above to save it to your profile.

If you enjoy learning new things and working with numbers, then you will never get bored with Power BI. Just when you think you have it mastered, along comes a new challenge that sends you back to do a bit of research. As Albert Einstein said: “The more I learn, the more I realise how much I don’t know.” 

In this article, I share some of the resources that I have found most useful. I try to present them in the order in which you might find them useful as you progress your learning journey with Power BI.

1. Fundamentals

I took a Microsoft variation of the Analyzing and Visualizing Data with Power BI | edX course in January 2015 when I first committed to learning about Power BI. It is free (you can pay extra for a certificate) and provides a step-by-step self-paced guide through the components and capabilities of Power BI. The course provides you with a great overview of what is possible, and the confidence to continue. On completing this course, you should be able to start building and publishing some basic Power BI reports. It is self-paced with sample data and exercises and the publishers suggest that it will take four weeks to complete when applying 10-20 hours per week. I would recommend this for anyone starting out with Power BI. It won’t make you an expert, but it will give you a great foundation on which to build further knowledge.

2. Continuous reference

There are two main languages that you will use on your Power BI reports: DAX (Data Analysis Expressions) and M (Mashup Language). Between DAX and M there are nearly 1,000 functions at your disposal. For that reason, whenever I am building a Power BI report, I would nearly always have two tabs open in my browser to the Microsoft online function reference pages.

M is used in Power Query to get and shape the data that are available in your reports – in other words to create your dataset. DAX is used in Power BI to create your visualisations and reports from this dataset. For someone coming from an Excel background, DAX will appear quite familiar but is designed to operate at a higher level, working with tables columns and rows, as opposed to cells. M will be less familiar to you unless you have already been working with Power Query in Excel. The good news is that although M may appear quite inaccessible to start with, you can “write” a lot of M code without even realising it because of a very powerful wizard-style interface that will generate a lot of the code for you automatically. You will, however, at some point need to learn to use some M functions. 

3. Any video or blog by Matt Allington

I first came across Matt Allington a few years ago on a YouTube video where he was giving some tips about preparing your data model in Power BI and how to lay it out visually. I think that Matt is a great communicator. He presents in a very simple, clear way that is very accessible to someone who is just starting with Power BI. I have since often stumbled across a blog of his giving some tip or trick. The pattern is the same, with good communication and simple, clear explanations.
Here is a great video from Matt, with a one-hour introduction to DAX. It is an excellent guide, but I think that the title is wrong because he talks about much more than DAX. But hey, that is a high-class problem.

4. Tips and tricks – Curbal by Ruth Pozuelo Martinez

Ruth does an amazing job. Every week she does three new videos, and now she has over 900 videos on her Curbal channel on YouTube. Her videos tend to be quite short (a few minutes, maybe 15 tops), answer a very specific question, and present very practical solutions. Ruth assumes a certain level of knowledge of DAX or M, in other words, “given that you already have a basic understanding of DAX/M, here is a quick way to solve the following problem…”.

This is a classic: How to filter a table to show only most recent date by group in Power Query – YouTube. In just four minutes, Ruth shows you how to do something that might take you several hours, or more, to figure out for yourself.

5. DAX details – SQLBI by Marco Russo or Alberto Ferrari

In my opinion, the articles from SQLBI are best appreciated by someone who has already been working with Power BI for a while and faced a few challenges. Marco Russo and Alberto Ferrari are the two authors of The Definitive Guide to DAX (see below) and are world-renowned in the field of DAX. They have a burgeoning content library of articles on their website and videos on YouTube. Their presentation style is very precise and detailed. For anyone looking to get a deeper understanding of DAX, the guys at SQLBI are most definitely worth following.
This DAX 101 video by Alberto Ferrari provides a two-hour introduction to DAX. It is one of the first pieces of online content that I watched and to be honest at the time it took a few goes for the main points to sink in. It is nonetheless a great video. Note that this video is featured on microsoft.com at Learn DAX videos – DAX | Microsoft Learn. There are countless other videos by the team at SQLBI. 

6. Learning consolidation and deeper reference

If you really want to master DAX, then The Definitive Guide to DAX by Marco Russo and Alberto Ferrari is for you. I recommend reading this after you have already got a bit of experience with Power BI. It is one thing to look up a DAX function on the Microsoft site, and it is another thing to really understand that function at a deeper level. CALCULATE, for example, is explained as a four-minute read in the DAX Function Reference website, on a single page. In this book, there is an entire chapter on CALCULATE that spans 36 pages. 

This highlights one of the aspects of DAX. It is a very accessible language to someone coming from an Excel background, and you can achieve quite a lot with a relatively low level of knowledge. There will come a point, though, when you will need a deeper understanding. The Definitive Guide to DAX really helped me to understand better the concepts behind the Evaluation Context and CALCULATE. Very simply, the Evaluation Context (that combines a row context and a filter context) defines what data are seen by a DAX expression and CALCULATE enables you to manipulate the Evaluation Context.

It is using concepts like this that enable you, for example, to create a generic “YTD” calculation that works by selecting values in Disconnected Tables (see below) in your report to set the Fiscal Year and the closing month.

7. Disconnected tables as switches

In your financial report, you most likely want to include options to select a date range and other aspects of the report. In the image below, I am using slicers on the right-hand side of the report page to drive the behaviour of the report. The tables behind these slicers are “disconnected”. That is, they have no relationship to any of the other tables in the report. Their purpose is to capture the user intent and, based on that, to modify the filter context of the report.

Graphical user interface, applicationDescription automatically generated

 

I first came across this technique in a webinar hosted by Andrej Lapajne, CEO of Zebra BI. I have been unable to find that original webinar but would like to give Andrej full credit because it is a technique that I use all the time. Here is another video by Andrej showing the technique, also one from Curbal that goes more into the DAX aspects.

8. Chris Webb’s blog

Chris Webb has written countless very good articles about getting the best out of Power BI. He has a very commendable style, and Chris Webb’s BI Blog combines very simple conclusions that are easily understandable by a beginner, with a lot of precise detail about how he came to those conclusions.

Take a look at this example: Chris Webb’s BI Blog: Optimising The Performance Of Power Query Merges In Power BI, Part 1: Removing Columns Chris Webb's BI Blog (crossjoin.co.uk).

The conclusion is very simple and understandable for a beginner – that removing unwanted columns from two tables before a merge will improve the performance (speed) of that merge. For more advanced users, Chris goes into detail about what he did to test and validate his conclusion. 

9. Imke Feldmann – The BIccountant

Imke Feldmann, aka The BIccountant, has produced a vast library of content relating to Power BI, with a particular focus on finance. Her articles go into a lot of detail and are well researched. The article from her blog that first caught my attention is this one from 2017: Easy Profit & Loss (and other account) statements in PowerBI and Excel – Part2 – The BIccountant.

The ah-ha moment for me was to see how you can use a mapping or bridging table to link the custom presentation of an Income Statement to the underlying accounts. It has simplified my DAX measures and improved the performance of my reports. As I am writing/rewriting from scratch, I avoid using bidirectional filters and create base measures that refer to the mapping table, but the principle is the same. Thank you, Imke!

10. Decluttering the canvas 

An eternal problem for a Power BI report developer is to optimise the space on the report canvas. For financial reports there are three common space problems: Columns, Rows and Slicers.

There are techniques that I use dynamically to select what columns are displayed (such as in the example above you can chose between Prev. Year, Budget, Forecast or Scenario) and this gets you so far. However, in turn this adds to the number of slicers on the canvas (and the space they take up) and sometimes you simply need lots of columns. For example, a particular Balance Sheet presentation might have 14 columns: Opening Balance, 12 x monthly movements, Closing Balance. This will pretty much take up the whole canvas. You might add a switch to display the numbers in thousands (so reducing the width of each column), but this will also add another slicer.

Hide/unhide your slicers

I recently came across this nice technique to use Bookmarks to hide/unhide your slicers. In my opinion this is an excellent video. Abhishek Agarrwal explains very clearly what he is doing and how it works in just over 12 minutes. You still need to find space in your report for a button to show the slicers, but I have found this technique very useful. It looks like he also has a lot of other great content on his channel.

Reduce the row count

An Income Statement with 30 rows does not make for a great presentation in Power BI. There are a few ways to handle this. Firstly, you can use the technique outlined by Imke Feldmann, above, and create a new summary view for your Income Statement, and the mapping that goes with it. This is fine if you want always to present the same view(s). 

Another technique that I found was from Alberto Ferrari of SQLBI to display the Top-N Plus Others in a table. Using this technique, you can create an Income Statement that say, in the Overheads section just shows the top six by value and bundles the rest into “Other”. This technique is great if you want to focus on the larger items but may not produce a consistent presentation from one month to the next. I also had difficulty getting the drill-through to work in a meaningful way on the “Other” row.

A third technique that I have working but need to find the time to write up (so watch this space – maybe it will be “R is for Reducing Rows”) is a combination of Feldmann’s technique and Ferrari’s. That is to select dynamically via a drop-down slicer the rows that you want to bundle into “Other”. Using this technique, I was also able to solve the drill-through problem. 

Summary

Whenever you create a Power BI report and put it into the hands of a user or client, you know there is a better way to do what you just did. It could be a more efficient way to structure your DAX code, a cleaner, more intuitive way to present your report, or a way to speed up the report refresh times. The more you learn, the more you realise what you don’t know, and this will drive you to learn more. It is unlikely that the general problem that you are trying to solve, or a related problem has not already been solved before and blogged about. Once your understanding of Power BI is high enough to frame the question, then you will find an answer that will either directly give you a solution or inspire one.

 

Replies (2)

Please login or register to join the discussion.

avatar
By ChristopherMacNeil
24th Nov 2022 05:29

this article need some more info

Thanks (0)
avatar
By freamelucas
01st Dec 2022 07:17

When you're learning something new, it's easy to get overwhelmed by the sheer volume of information available. The best way to overcome this is to break down each new skill into manageable steps and use resume help reviews to find the best solution. You don't need to learn everything at once, but you do need to focus on a few pieces of information that you can use as building blocks for your learning.

Thanks (0)