Uncategorized

  • 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
  • How to create a toggle button in Power BI

    In this blog post I will be showing you how to use a toggle button in Power BI. A toggle button in Power BI allows users to quickly switch between two options. Our first step is to create a toggle button as Power BI doesn’t have one by default. You can import one but by creating it yourself, it gives you more control. Our toggle button is created by having two images of identical size over the top of each other and using bookmarks to flip…

    » Read more
  • Change the Compatibility Level of a Power BI report

    A number of new features in Power BI are limited to newer Power BI datasets, which Microsoft track through the use of Compatibility Levels. 1500+ for SQL Server 2019 compatibility 1400+ for SQL Server 2017 compatibility 1200+ for SQL Server 2016 compatibility If you want to, for example, connect to the xmla endpoint of the dataset from SQL Server Management Studio, then you need level 1500 or greater. If it’s lower you’ll get the…

    » Read more
  • Automate changing SSIS connection at runtime

    Recently a customer came to us with an issue: “We have a primary/secondary AlwaysOn failover cluster, we want the data warehouse ETL to always pull from the secondary read-only replica. When the primary fails over, how can we detect this and repoint the ETL to the new secondary?” This led us to the following qualification steps:1. Is the secondary server accessible? a. Yes – Use it. b. No – Try the primary server.2. Is…

    » Read more
  • Increasing DAX Filter Speed and Efficiency

    To understand how a filter can affect performance, first we need to understand what a filter is. A filter is a temporary table that is created on demand, held in memory, and then used as a reference for the function being filtered. The rows of the temporary filter table are all DISTINCT. This is because for the purposes of filtering, the engine doesn’t care whether there are 1, 2, 3 or 250 occurrences of a value in table, only whether it…

    » Read more
  • How to create the Fibonacci Sequence in Python

    This blog post will go through how to create the famous Fibonacci sequence in Python. Each number in the Fibonacci sequence is the sum of the previous two terms. The sequence starts: 0, 1, 1, 2, 3, 5, 8, 13, … and carries on infinitely. The Python code I used is: The first two terms are entered into our list, then we specify the number of terms wanted in our sequence. Next, we loop through from 2 to the number of terms we wanted appending…

    » 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
  • Join 2 Python lists together using nested loops

    In this blog post I will show you how to join two 2D Python lists together. The code is in the screenshot below. Lines 1 – 2 are two lists that are going to be joined, line 3 is an empty list where the output will be appended to. Lines 4 – 5 are two loops (one nested inside the other) which cycle through the records in both lists, line 6 checks whether the first items (index 0) in the two records from each list that are currently i…

    » Read more
  • Dynamic Date Formats in Power BI

    Which date format styles should we use if we are building a report that is being consumed internationally? Remember, 01/12/2021 is December 1st or January 12th depending in which part of the world it is being read. The decision may be taken from our hands if there is a company policy in place. If the company is based in the USA, for example, they may choose to use US formatted date fields as a standard for reporting across the entire business,…

    » Read more
  • Power BI – Enable Load

    In Power BI Power Query there is an option to enable or disable whether a table is loaded into the report. The option ‘Enable load’ can be found by right clicking on the table. Typically, by default, the load is already enabled. There is also an option ‘Include in report refresh’ which lets a user stop a table from refreshing when they refresh the entire report. This maybe useful for static tables or tables that are large which take a…

    » Read more