Archive - Blog Posts on Intermediate

Killer Tips to Write Harder Measures - Level: Intermediate Background I was reading the questions at http://community.powerbi.com today and saw an interesting question.  In short the OP wanted to know if there was a way to identify (via a measure) if the row in a table had a colour =”Blue”, but also to check that there were no other rows with the […] Read More
DAX UNICHAR Function in Power BI - I first learnt about the new DAX UNICHAR function in Power BI by reading Chris Webb’s blog (which was also the inspiration for this article today).  One of the uses of the UNICHAR function is to be able to include non text characters inside visuals.  When you combine a logical set of these codes (icon sets) you […] Read More
Using Variables in DAX - Level: Intermediate The variable feature in DAX has been around for more than a year and I have simply never got around to writing an article about it – I am changing that today.  Let me start off with some version information and syntax and then I will show you are real life example where I […] Read More
New Functions – GENERATESERIES, SELECTEDVALUE - Level: Beginners/Intermediate In the August 2017 update of Power BI Desktop Microsoft released the new What If feature (first demonstrated at MDIS in July).  This new What If capability uses two new DAX functions to complete a task that you have always been able to do manually.  You still can of course still do it […] Read More
When to Use SUM() vs SUMX() - Level: Beginners/Intermediate It has been 2 years since I first wrote an article about SUM() vs SUMX().  I decided to write a new article to refresh this topic and add in the things I have learnt over the last 2 years. SUM() vs SUMX() First let me start with an overview of both of these […] Read More
The ALL() Function in DAX - Level: Intermediate In this article I am going to write about the ALL() function, show how it works and explain a common use case.  I will also cover the unexpected behaviour that occurs when using the ALL() function in Power BI Desktop – more on that later. For this article I will use my simplified copy […] Read More
Understanding Power Query Combine - Level: Intermediate Sometime late in 2016 Microsoft deployed a new “Combine” feature in both Power Query for Excel (Get and Transform) and also in Power BI Desktop (Get Data).  The new capability makes it easier to combine multiple copies of similar workbooks into a single table without hand coding M Functions, but unfortunately it is now […] Read More
My MDIS Presentations - Level: Beginners/Intermediate As those that read my blog regularly would know, I have been at the Microsoft Data Insights Summit in Seattle Washington for the last 2 days. I had the privilege and pleasure of presenting two sessions at the event. For those that were not able to attend, here are the videos of my […] Read More
How and Where to Get Help for Power BI - Level: Beginners/Intermediate Regardless of what stage you are at in your Power BI/Power Pivot/Power Query journey, at some stage you are going to need some help.  Believe me I am no different to you – we are all learning and we all need help sometime or another.  There are basically 3 options that I want […] Read More
Uses for DAX as a Query Language - Level: Intermediate This is the final article in my series about DAX as a Query Language.  In my first article I introduced DAX as a Query Language and then in my second article I went in deeper to the specifics on how to use some of the more useful DAX query functions.  In this final article […] Read More
DAX as a Query Language Part 2 - Level: Intermediate In my last article I introduced DAX as a Query Language and explained why learning to query your data model can be useful.  I covered the basics about using a tool like DAX Studio to query a database and return a table of data as a result.  In this article I am going to […] Read More
An Introduction to DAX as a Query Language - Level: Intermediate I have written quite a few articles that contain embedded references to DAX Queries and DAX Table functions in the past.  Today I am writing an article that introduces the DAX query language from the beginning so you can learn how to use it and how it will help you to become a […] Read More
SQL Saturday Melbourne Feb 2017 Materials - Level: Intermediate I am currently in Melbourne for PASS SQL Saturday 2017.  SQL Saturday is an annual one day conference event that occurs in many major cities around the world.  It is focussed on helping SQL Server professionals learn more about their profession and also network with other like minded people.  I have a lot […] Read More
Use Power Query to Compare Database Records - Level: Intermediate I was helping a user on http://community.powerbi.com this week. The user had a list of service desk tickets for multiple customers and the requirement was to identify when a customer opened a second new ticket within 7 days of closing a previous ticket.  This is not an uncommon scenario so I thought it would […] Read More
Extract Tabular Data From Power BI Service to Excel - Level: Intermediate Someone asked me a question yesterday about exporting data from the Power BI Service into Excel.  There are a few options to do this however they all have their problems (these problems are not covered in great detail in this post). Power BI has an inbuilt export data feature (there is an export limit […] Read More
Data Modelling with OneDrive Source Files - Level: Intermediate Last week I shared how it was possible to easily create an online survey using Excel for O365.  Once such a survey is created, the data can be imported into PowerBI.com and used to create a simple report.  The biggest issue with directly connecting to OneDrive like I showed last week is that you […] Read More
Import Tabular Data from PDF using Power Query - Level: Intermediate Today I am sharing a process I developed that allows you to import tabular data from a PDF document into Excel (or Power BI) using Power Query.  I didn’t want to purchase software to do this task so I started experimenting on how I could do it with the tools I already have, and […] Read More
Power Query to Combine Web Pages - Level: Intermediate There was an interesting question this week on http://powerpivotforum.com.au asking if there was a smarter way to user Power Query over multiple identical web pages to scrape the data in a single query.  I have been meaning to blog about this for a while, so it is a great opportunity for a mini-Friday […] Read More
Measures on Rows – Here is How I did it - Level: Intermediate You may or may not be aware that it is not possible to put Measures on rows in a Matrix in Power BI. But I came up with a trick that makes it possible, so read on to find out how. Measures Can Only be Placed on Columns First the problem. The only […] Read More
Power Query Over a Command Screen Output File - Level: Intermediate I spent a lot of last week helping to configure Power BI in preparation for go live for a client.  One of the important things to do when designing a Power BI solution is to make sure you have a good design for your user security access.   Today I am going to share […] Read More