2020 has been a great year to dive deeper in Power BI and new emerging features and tools. I’ve enjoyed learning from reporting tips, external tools, updated books, more M, culture, and the list goes on. All great resources, and a clear sign to me of a maturing product getting stronger.
With all of the innovative changes and add-ons this year, I wanted to take a look back and revisit my own top 10 go-to practices I’ve picked up from the Power BI community.
I enjoy everything from data to dialogue, so this list starts with some Power BI specific features, then moves to lessons learned that keep me motivated and telling a better data story. All have been instrumental in my journey.
As always, thank you for looking, and please feel free to share your own favorite lessons learned.
10. Not your every day DAX:
Beyond my daily use of CALCULATE, I’ve made a habit to try out one less frequently used function each week (TREATAS for example). I make a point to either tackle something new (to me) in the SQLBI DAX Patterns book (see newly released version here), or explore SQLBIs DAX Guide website (here). I then open a ‘mini model’ I keep handy in order to practice and save any notes I may want to reference later.
Applying what I learn in this small and controlled model helps me identify any challenges or unexpected results in advance of using the functions on larger datasets. It’s been a great way for me to better understand new DAX while also cataloging what I’ve learned along the way.
9. Buttons and Bookmarks:
I heard enough about adding “slide out panels” in my report that I had to try this for myself. Adding bookmarks and buttons that allows me to add optional visuals was well worth my time and gives users a more polished user experience.
This involves opening up the Selection tab, grouping visuals together, and establishing the display order. After you have all of the visuals positioned, you then create Bookmarks that serve as pre-sets to remember what should display (or hide) when a button is pushed. Interaction with the buttons has really freed up space and brought a more web like experience for our users.
I leverage this feature in two ways; first, to hide or call out a dedicated slicer panel that would otherwise take up valuable space. Having the option to send a block of report slicers away is very handy for a full and clean report display.
Second, I use buttons and bookmarks when I want to display an alternate visual in the same space. By using buttons that drive user experience, users are able to quickly toggle between the two visuals.
I used this video from Guy in a Cube to get this use of bookmarks down.
8. Tool-tips:
This feature can be a great alternative to drill-down in a matrix visual when you want to give users quick small snapshots of other detail or visuals. This feature is handy with room to grow in my opinion.
I would love to see the report user have better control over how and when the tool tips displays on the report. It would also be nice to have the ability to scroll further down in the tool tip pop-up visual itself. If you feel my pain, go vote here 🙂
7. Parameters with disconnected tables
On the report side there are few tools that spark user engagement better than giving your user the ability to change the scenario and the displayed result. There are many ‘on the fly’ conversions you might want to use… currency conversion, rounding in thousands etc. I’ve found it effective to use disconnected tables to display “what if” analysis. Many ways to implement this; changing discount levels, increasing margins, etc. when measuring performance this has gotten people in our business to sit up and engage.
To find out how this can bring interaction and engagement to your reports, check out the brand new Patterns site from SQLBI (here).
6. “M”y Transformations in Power Query:
When I was starting to learn DAX, I ventured over to check out M in Power Query. At the time, I didn’t appreciate the need for what seemed to be a foreign language in M. I shut the book and went back to DAX.
In time I’d dabble enough in the advanced editor and sought out solutions that requires custom M, and gave it another go. What really won me over the was Gil Ravi’s book Collect, Combine, and Transform. This book gave me a new perspective on Power Query, and today I consider M just as much as a super power as DAX.
The ability to Group, Pivot, Merge, and Append from the PQ interface also helps to establish dynamic dimension tables in my model. By performing these transformation ahead of loading my data, I make better use of the compression in the Vertipaq engine, and it also helps reduce additional DAX I would otherwise need to create within my model.
5. Variables and formatting DAX:
Variables took a bit for me to fully appreciate early on. I had the wonderful opportunity to attend an SQLBI live course where I watched Marco use variables to work through different ways to solve business problems.
I had heard about how variables helped us avoid the dreaded EARLIER function; but this was the first time I saw variables used to construct and test complex measures in manageable steps. If you come from Excel, I compare variables in DAX to ‘helper columns’ in Excel which also allow you to construct long formulas without getting lost in the string.
I’ll also add the during this same course Marco made it a point to mention the importance of formatting DAX in a way that was helpful for you. As a true Excel person, I had always typed my measures all in one long string. As my DAX became more complex, I found formatting my DAX using DAX Formatter very useful. I now want this format ability in Excel!
To find out more about using variables in your DAX measures click here and here.
4. Custom time intelligence:
We use a fiscal calendar at my work. Very early on I was convinced by Rob Collie in his Power BI book when he referred to the custom time intelligence FILTER as the Greatest Formula In The World (GFITW). I practiced and eventually this became second nature. I use these formulas exclusively.
Combine this custom formula with the use of variables to help define and test your measure, and you pretty much have total flexibility over time intelligence. To find out more about the GFITW, check out this blog post from Power Pivot Pro here or check out Rob’s book below.
Whenever I load my Date table from Excel, I also create a calculated column in the date table that serves as a ‘flag’ field for the days that have associated sales in my fact table. I use CALCULATE in the column to pull related sales for each day into my calculated column.
Next I wrap an IF statement around my CALCULATE in order to check if my new filter context returns values from the fact table or not. When there is a value present (a non-blank result), I replace the returned value with the text “Flag”, otherwise BLANK.
This helps in two ways. First, I’m able to drop that flag field into the filter pane of my report, and filter my report (including slicers) to reflect only those dates contained in my fact table.
Second, that Flag column also simplifies how I write custom time intelligence using DAX. By being able to add a measure filter based on my text value “Flag”, I ensure that my measures don’t extend too far into the future (or past) when isolating the max and min month and year from my fact table.
3. Involvement in the community:
I can’t tell you how much being involved in the Power BI / Power Platform community has meant for my career development.
As I was starting in Power BI, I really had no reference as to how I was tracking in my progress. I really enjoyed finding and attending my local Portland Oregon Power BI User Group. I remember visiting my first meeting when DAX was just starting to work its way into the presentations. Brian Grant spoke a bit about ggplot2 and R. Today Brian has just released over sixteen hours of in depth DAX training here on YouTube. Over the years this group has grown with Power BI and has been a great resource for myself and others to learn and contribute.
I also wake up on Saturdays to join the Guy in a Cube “Live Stream”, as well as the “Two Alex’s” stream to stretch my knowledge. This is a fun way to learn and chat with others passionate about Power BI.
I’m an Excel guy turned Power BI developer… Much of the architecture topics are over my head at times; however I manage to pick up new insights and ideas every week. It has been a great resource and motivator for me. Check out the Guy in a Cube channel for more information.
2. Putting my hands on the keyboard:
As I mentioned earlier in this post that there had been a lot for me to absorb, especially in 2020. My M.O. is to gain book knowledge, write some of it out in my ‘mini model’, then wait for the perfect opportunity to arise at work. However there have been pivotal changes and additions in 2020 where waiting for the perfect opportunity doesn’t work. No better example of this for me than learning Tabular Editor.
I need to give a big shout out to another Power BI evangelist our there, Alex Powers. Alex saw the value of learning Tabular Editor early on and ran with it. He promoted learning this tool with a one hour workshop on Git Hub. He includes example files, instructions, and video tutorials. With the encouragement from him and others, I’m convinced this will become a major shift in how we author DAX going forward.
However, the main point here is not Tabular Editor, but a reminder Alex points out during his training; the need to “Put our hands on the keyboard… and get that muscle memory going”. This encourages me to not passively learn this stuff, but to continually try something new that may be outside of my comfort zone. This is a discipline I’ve had to build over time, and has taught me to appreciate the learning process as much as I appreciate the final result.
Alex is very good at promoting new and emerging tools that benefit the Power BI developer. His one hour workshops give developers a nice head start in working with these tools. If you are interested in learning more about how his workshops can help you, visit Alex at the Microsoft GitHub Workshops here.
1. From data to dialogue:
Wow this blog is longer than I expected it to be 🙂 If you are still with me, bravo!
Asking this Accountant to become a data storyteller was no small task. Through trial and error, we tested models, adoption, culture, and data storytelling.
I’ve learned that at the end of the day that data alone does not create change; however data combined with effective dialogue between people does create change. Nothing helps get us to collaborative change better than effective dialogue.
My best opportunity to witness this was when given the chance to travel to our external customers and explore the value of Power BI as a value added tool. I essentially got to visit our different regions and show off Power BI. At first I presented it no differently than a spreadsheet; I controlled the direction, and got very little engagement from it. As I went back to the drawing board, I’d add more color, interactive features, and built a data story. With each return visit I’d share a bit about how we could explore the information, then just sit back and allow the customer to choose our direction. I started noticing a stronger response and better engagement from the customers. We eventually landed on a report and dialogue combo that brought us both more value and collaboration than ever before.
This taught me that for this purpose (sharing insights with non-analysts), there was value in making a quick visual connection over walking through each number on a spreadsheet. Spreadsheets are great at documenting the business; however dashboard visuals were better at telling a story and problem solving together. I don’t pretend to understand the psychology behind this, but can assure you that I’ve seen this play out repeatedly as an added value for our business.
I’d be interested in hearing what you’ve learned this year and how it’s helped you and your organization. If you are looking to learn the fundamentals of Power BI, I would encourage you to look at our Learning Resource blog post here. Take care and have a great week!
Leave a Reply