Power BI

  • Previous Row Value DAX

    In this quick blog post I’m going to show you how to use a hidden DAX function to get a previous row value with just a few lines of code. It’s that hidden that intellisense doesn’t even recognise it! I’ll use an example of some fictional race results from some well-known cartoon characters! Lets assume that in this scenario we wanted to analyse the time difference between 1st and 2nd place, 2nd and 3rd place etc in the race scenario…

    » Read more
  • How to Trigger a Power BI Dataset Refresh via Azure Synapse Analytics in 3 Simple Steps.

    In this blog post we’ll explore how to trigger a Power BI Dataset via Azure Synapse Analytics in 3 simple steps: Step 1: Create a new security group called “AzureSynapsePowerBIIntegration” in your Azure Active Directory. Next add the name of your Azure Synapse Workspace as a member of your new security group. This will be your service principal. Step 2: Login to powerbi.com and in the top right hand corner locate “Settings” and…

    » Read more
  • Download Power BI Reports using PowerShell

    In this blog post I will be sharing a PowerShell script that allows multiple Power BI reports to be downloaded at once. In the Power BI service, there is no way of downloading multiple Power BI reports at once. Therefore, users must download files one by one which is slow, time consuming and inefficient. Thankfully, there are ways around this, one of which is using PowerShell. The script is downloadable at the bottom of the page as a txt file.…

    » Read more
  • Combining Queries from Multiple Sources in Power BI using Merge and Append

    It is always good practice to do as much data preparation as close to the sources as you can before importing or connecting them to your Power BI reports, but what if there are circumstances where this isn’t possible? I had an issue recently where a third-party application had been updated and both the new and legacy versions were being used side-by-side. Logging data from both versions was being written to two separate Azure SQL databases.…

    » Read more
  • Making the DAX Engine Work for You

    In my last blog post which can be found here, I demonstrated that it’s important to limit your DAX filters to specific columns rather than entire tables, now we know that we can and should do it, let’s explore why and how it works. There are numerous ways to write an expression that will give the same result set, but why are some ways more efficient than others? To answer that we need to know a little about what happens under the hood,…

    » Read more
  • Replicating SQL windowing functions in Power Query.

    I’m sure you’ve all heard of windowing functions in SQL Server? But what if you need to replicate these windowing functions in Power Query? Here I have a sample CarSales.csv dataset of car sales per brand, per year, per month and the number of units sold. I will use this dataset to demonstrate how we can replicate the windowing LAG function in Power Query. In my example I’d like to calculate the month on month sales growth per car…

    » Read more
  • Variables in DAX

    Variables can simplify your DAX code, help with debugging and help improve performance. To use variables in your DAX measure you need to declare a variable using the VAR keyword, give the variable a name, and then assign an expression to the variable. You can use multiple variables in a measure but when using variables you must use a RETURN statement to specify the final output to be returned. To show how to make use of variables I’ll go…

    » Read more
  • How to change the summarization of multiple columns in Power BI.

    Have you ever been faced with the issue where Power BI automatically adds a sigma symbol to the front of your column if it detects it’s a data type of integer or decimal? Now this isn’t too much of an issue if you’ve imported only a few columns but what if you’ve imported lots? Going through this column by column can become tiresome! Luckily there is a hack, not only to turn summarization to “None” on multiple columns at once, but…

    » Read more
  • How the UNICHAR() DAX Function Enhances Power BI Reports

    Enhance your Power BI reports and heighten your user's experience with icons and emojis

    » Read more
  • How to export more than 30,000 rows of data from Power BI Desktop.

    Have you ever wanted to export a table from Power BI Desktop into Excel just to make sure the DAX you’ve written is performing as expected but ran into this error message: “Data exceeds the limit – Your data is too large. Some data sampling may occur”? Most probably this has occurred because you’ve got more than 30,000 rows of data in your table that you’re trying to export. In the example shown below I’ve actually got 30,001 rows…

    » Read more