Archive - Blog Posts on Intermediate

Simple Filters and Syntax Sugar in DAX - Level: Intermediate There is a concept in the DAX language called “Syntax Sugar”.  Simply put, the developers have created simplified versions of more complex formulas to make it easier for people to learn and use the DAX language.  Here are a couple of examples Total Sales Syntax Sugar = SUM(Sales[ExtendedAmount]) Total Sales Full Syntax = […] Read More
calendar overrun in Power BI
Calendar Over Run While Comparing Last Year’s Data - Recently, I was helping someone on Power BI community forum on the issue of calendar over run while comparing values with last year’s data. I thought it would make a good article to share with others. The Problem I will illustrate the issue using the AdventureWorks database (I need to make some tweaks to do this).  […] Read More
Copy measures between PBIX files
Copy Measures Between 2 Power BI Files - I have often been asked (and wondered myself) if it were possible to move all the measures from one Power BI PBIX file into another.  Imagine you have 2 PBIX files with similar data models (similar enough that the measures will be substantially the same).  It doesn’t matter how you got to this point – […] Read More
Import First Sheet with Power Query
Import the First Sheet in a Workbook with Power Query - My series about common patterns for loading data using Power Query received quite a lot of interest over the last few weeks.  You can review those articles in the links below Combine all files in a folder with Power Query. Load the Latest Version of a File with Power Query. Combine all sheets in a […] Read More
Combine All Sheets in a Workbook with Power Query - There are quite a few different common business scenarios where you get data in the form of a file on a regular basis.  The 3 common patterns I use most often are. Each day/month/year you get a new file containing incremental data.  All the data in all the files need to be combined and loaded. […] Read More
Load latest file in Power BI
Load the Latest Version of a File with Power Query - There are quite a few common business scenarios where you get data in the form of a file on a regular basis from some source system and you need to process these files into your Power BI reports.  There are 3 common patterns that come to mind. Each day/month/year you get a new file containing […] Read More
Rename Blank in a Slicer in Power BI - A common problem I see some people struggle with in Power BI is when a slicer contains a (Blank) record for some reason.  The cause of the problem is not obvious and hence it is not clear how to fix it.   This article describes what causes this to occur and also how to fix it […] Read More
When to Use SUM() vs SUMX() - Level: Beginners/Intermediate (Updated Oct 2018). This is a further (third) update/edit to an article I first wrote 3 years back and then updated a year back.  When I update my articles I simply change the original post with the latest data – that way only the current/latest version is available preventing people stumbling on old […] Read More
Create a Power BI Slicer Panel - There is nothing worse than having a Power BI report that has 50% of the space taken up with slicers.  When this happens, you only get half the page to visualise the actual data.  But on the flip side, if you don’t have the slicers it can be harder for the report users to filter […] Read More
My Presentations at the MS Summit and Portland UG - Evaluation contexts in Power BI DAX I was in Seattle, USA in the last week of July participating in the Microsoft Business Applications Summit (MBAS). I love attending conferences like this and it was a privilege to present on a few topics over 3 days.  One of the presentations I gave was on the topic “Evaluation […] Read More
Extending Power BI With R Visuals - Level: Intermediate Matt: This is the second one in a series of articles from Parker Stevens. In this article he talks about using R with Power BI. If you missed his first article “Introduction to DirectQuery” you can read it here. Parker: Power BI comes with a ton of built-in features but eventually you might […] Read More
Extracting Complex Data from Websites - One thing I did a long time ago was set up my own forum called PowerPivot Forum. This forum is open to anyone to join and to ask questions and/or help others. If I have any spare time (decreasingly so these days), I help people out on the forum too.  It occurred to me that the forum […] Read More
Keeping Track of Changes on a Website with Power BI - Level: Intermediate A couple of weeks ago I posted a list of my ideas that I have created at ideas.powerbi.com and asked you to vote for them if you agree with the concept (please go back and take a look if you missed it).  I thought it would be interesting to keep track of the […] Read More
Toggle Overlapping Time Periods using the Calendar Table - Level: Intermediate. I had a great question pass my way last week while I was at the Microsoft Business Application Summit. The problem presented by Matt (a different Matt, not me) was that he had around 200 measures already written, but wanted to be able to toggle the report pages between YTD and Full Year […] Read More
Phasing Income to Future Dates - Level: Intermediate I do lots of live training, face to face and online, and I always encourage students to bring along any business problems they would like some help with.  Last week I had a great problem that Debbie shared with me that I thought was worth creating this blog article.  I have modified the […] Read More
User Specified Growth on an Investment using Power BI - Level: Intermediate I wrote an article earlier this year that showed how you can take a table of future growth rates (different growth rates each year) and then calculate the future value of an investment using DAX.  I had a follow up question from Steve asking if it is possible to allow the user to […] Read More
Using Power Query for Extracting Non-Tabular Data from Web Pages - Matt Here:  I first met Ivan via Twitter when he was living in Russia, and then in person when he moved to Australia to live in Sydney with his family. Ivan is a talented Power BI professional and I have learnt many things from him over the last few years.  Today Ivan is writing a […] Read More
Number of Days between 2 Transactions Using DAX - Level: Intermediate I got my haircut today (pretty spiffy one too, even if I do say so myself).  While I was chatting I asked my hair dresser “on average, how often should I get my hair cut”?  She told me (for men) around 4-6 weeks.  Then I got thinking (as I do), I wonder if […] Read More
Time Intelligence Using DAX - Level: Intermediate Last week I presented a webinar with Chuck Sterling (from Microsoft) on the topic of Time Intelligence.  This was a YouTube broadcast event that anyone could join live, however it has also been recorded and can be watched at any time (see below). This article is a follow up from that webinar with […] Read More
Introduction to DirectQuery - Level: Intermediate to Advanced Matt here.  I recently met Parker Stevens online through LinkedIn – he too is a Power BI fan, just like me.  Parker has shared a few techniques with me that I found very interesting.  These topics have not been covered on my blog before and hence I thought it was a […] Read More