Cagliari

When you need to report hourly, Daylight-saving time switches can be a real issue. Here’s how I managed to solve it.Photo by Jon Tyson on UnsplashUsually, we create reports with the granularity of Days or lower.Some time ago, I worked for a client which creates reports on the energy consumption of their production lines, which run 24/7.Their data is aggregated to 15 minutes.In such a situation, the switch to daylight saving time and back to solar time can cause issues in the report.For this article, I created a table…

Query folding is an important feature in Power Query to offload processing steps to the Source. With this feature, Power Query can reduce CPU and RAM consumption. Let’s look at how we can use it.Photo by Element5 Digital on UnsplashQuery folding is the mechanism to push Power Query transformations back to the source to reduce the load of Power Query.This works only for some data sources, namely relational databases and sources which work like relational databases.For example, look at the following diagram, which shows…

You can find several useful functions in DAX when looking at the current Filter Context. Here are some examples of these functionsPhoto by Ran Berkovich on UnsplashThere are a few DAX functions to look at the current Filter Context.In this article, we will look at the following functions (In alphabetical order):ALLSELECTEDFILTERSHASONEFILTERHASONEVALUEISCROSSFILTEREDISFILTEREDISINSCOPESELECTEDVALUEI will provide a link to the corresponding article on DAX Guide and examples for each function on how to use them and how they…

What if your users want to select one year but see four years of data based on the selected year? Let’s see how to build a solution to tackle this requirement.Photo by Tingey Injury Law Firm on UnsplashOne of my clients asked me to build a report in Power BI with a Slicer for the reporting period and a visual, which shows four years of data based on the selection in the Slicer. Two years before the selected period, the selected year, and one year after the selected year.When we look at a time series, the data could look…

What happens when we have to broaden our scope to include more orders than visible in the current Period?Photo by Isaac Smith on UnsplashOne of my clients asked me the following two questions:I would like to count all orders, including the open orders during a specific periodI would like to categorise my customers based on the order count over timeThese two questions seemed easy, but after some thought, I had to think twice about the possible solutions.To solve these two questions, we must manipulate the Filter Context to…