Power BI Table Totals Not Adding Up? Here’s Why!
October 4th, 2023 in Blog, DAX, PowerBI, UncategorisedEver built a table containing measures in Power BI, done a quick sanity check on your table totals and noticed they’re incorrect? Let’s explore why this may be. Here we have a simple table, built from data in the AdventureWorks database. It shows the three product categories, the Total Sales and Average Order Total of each category: As you can see, the Total Sales column adds up perfectly, but the average is just plain wrong. 19.42 +…
» Read moreWhy Are There Gaps in the Identity Column in My SQL Database?
May 17th, 2023 in BlogAll too often there are questions asked about missing records that are identified by gaps in the identity column of a table. If a table, ordered by the identity column, shows “1, 2, 3, 5”, record 4 must have been deleted right? Well, although that is a possibility, it’s not a safe assumption as there are other causes of these “missing” IDs. Let’s start with a simple table, it only has 3 columns, the first of which being an…
» Read more#This vs @That – Should I use a Table Variable or a Temporary Table?
April 17th, 2023 in Blog, SQL ServerWhen working with SQL Server, it’s not uncommon to need to store data in a temporary table or table variable. While both options can be used to accomplish the same goal, there are differences between the two that can affect performance and the ability to write efficient code. Let’s explore the differences between table variables and temporary tables, and when to use each one. @Table Variables Table variables are declared using the…
» Read moreUsing ChatGPT as a Development Tool
January 4th, 2023 in AI, Blog, Cloud, DAX, Just For Fun, OtherAs a developer, you are always looking for ways to streamline your workflow and improve efficiency. One tool that you should consider adding to your development toolkit is ChatGPT. What is ChatGPT? ChatGPT is a chatbot developed by OpenAI that uses the power of machine learning to understand and generate human-like text. It is trained on a large dataset of human conversations, which allows it to understand the context and nuances of language,…
» Read moreWhat are SQL Server Index Fragmentation and Index Fill Factor?
November 9th, 2022 in Azure, Blog, SQL Server, UncategorisedWhat is index fragmentation and how does it occur? It is important to know that SQL Server data is stored in data pages, with each page holding 8KB of data. There are two types of fragmentation, both are a result of these pages not being used as efficiently as possible. When you UPDATE or INSERT data on a page that is already full, SQL Server creates a new page. The information from the original page will be split 50/50 with half being added to…
» Read moreQuery Store Forced Plan Failures
July 28th, 2022 in Azure, Blog, SQL Server, UncategorizedQuery Store is a fantastic feature of both SQL Server and Azure SQL DB. It allows you to monitor how queries execute against the database which is invaluable for troubleshooting performance issues. More than that though, it gives you the option to force an erratic query to use a particular execution plan, this helps avoid queries from running with inefficient plans and provides predictability and stability on the server. Here we can see an…
» Read moreThe SQL Query Alias Conundrum – Order of Execution
May 17th, 2022 in Blog, SQL Server, UncategorizedSo, you have just written a query, hit execute and you have encountered an error: Invalid column name ‘[column name]‘. The column you’ve used in your WHERE clause cannot be identified by its alias. You’ve defined it at the top of the query and used it fine previously as your ORDER BY condition, so why can’t the engine recognise it? If you’ve ever written a SQL query, this should look familiar: SELECT DISTINCT FROM JOIN ON…
» Read moreCombining Queries from Multiple Sources in Power BI using Merge and Append
March 1st, 2022 in Azure, Blog, Business Intelligence, Power BI, Power Query, SQL Server, UncategorizedIt 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 moreMaking the DAX Engine Work for You
December 3rd, 2021 in Blog, DAX, Power BI, UncategorizedIn 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 moreIncreasing DAX Filter Speed and Efficiency
September 24th, 2021 in Blog, UncategorizedTo 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
Call today 0845 643 6463
