Cagliari

We aggregate data in Power BI all the time. Sometimes we want to query our data model manually or need intermediary tables within our Measures. Let’s look at how we can do this.Photo by Isaac Smith on UnsplashHave you ever asked yourself:What happens behind the scenes of a Power BI Visual?OrHow can I write a query to get the result shown in a Power BI Visual?OK, you can catch the Query with Performance Analyzer and copy the Query in a Text Editor or, even better, in DAX Studio.But do you understand what happens in the…

We use table variables in DAX all the time. But what when we need to calculate intermediary results and reuse them later in a DAX Measure? This challenge sounds easy, but it’s not.Photo by Mika Baumeister on UnsplashWe are using intermediary table variables all the time in DAX.For example, look at the following Measure: =VAR YTDDates = DATESYTD('Date')RETURNCALCULATE(,YTDDates)In this case, we generate a Year-To-Date table based on the actual Filter context with the help of the DATESYTD() function containing all the dates…

Imagine having a data set and wanting to see zeroes instead of empty cells. Sounds easy? Well, this case has some caveats. Let’s investigate it.Photo by Ramón Salinero on UnsplashA few weeks ago, I already wrote an article about this topic:In that article, I used Power Query to generate additional data rows to fill the data table and get the needed result.Now I found a much easier way to solve this challenge.First, I will explain the challenge. If you know my article linked above, the following section describes how I…

This title seems counterintuitive. Why should I ask for a result when there is no data? Go ahead to find out the client’s request and how I solved it.Photo by Emily Morter on UnsplashOne of my clients has a Dataset, which consists of Tasks, Subtasks a Measure for each task.In addition, each task has a Status.These States are:DoneIn ProgressPendingCancelledFailedAfter importing the data into Power BI, the result looks like this:Figure 1 — First result (Figure from the Author)But, sometimes, there are no Tasks or Subtasks…

We cannot apply conditional formatting for line charts in Power BI to control the colors of the lines. But sometimes we want to fix these colors. Let’s see how we can do it.Photo by Skye Studios on UnsplashIn my previous article, I explained how to control colors using DAX expressions:In that article, I referenced the IBCS rules to select high-contrast colors for column Visuals instead of the standard Power BI colors.One of my clients asked me how to apply these rules with Line Visuals.This question resulted in two…

We can add rules for coloring visuals for a long time now. But how can we use DAX expressions to control these colors and try to follow IBCS rules?Photo by Firmbee.com on UnsplashA few weeks ago, I published an article about Information Design:As a recap:IBCS is a company that created a set of rules for Information design.IBCS helps us use its rules to improve our reporting.I condensed the original 8 SUCCESS rules into three easy-to-use rules:1. What is your message?2. Use a consistent notation.3. Remove unnecessary…

I wrote an article on four security features in Power BI one year ago. In the meantime, we got some new Security features. Let’s investigate them.Photo by John Salvino on UnsplashLast year, on November 03, 2021, I wrote the following article:Microsoft has added two new Security features in Power BI since then.This is a good reason to return to this topic and update you.In the Article mentioned above, I listed the following four security features of Power BI:Privacy Levels in Power QueryRow Level Security (RLS) in Power…

There are several ways of distributing values. One way is to use a factor mapped to rows in a table to distribute them accordingly. Let’s dive into how to do this in Power BI with DAX.Photo by Robert Linder on UnsplashOne of my clients had the following question:I have data about projects with business partners. I can have multiple partners participating in one or more Projects.How can I create a Power BI Report in which I can see the amount of money my partners are spending per period?I created a small dataset to…

Imagine you want to show various Measures with the same Axes, for example, Time of Geography. Here I show you how you can do this in one Visual.Photo by Justus Menke on UnsplashImagine that you have many Measures, and you would like to see them side-by-side, all with the same Axes, for example, Dates, Geography, Product Categories, etc.You would need to add one Visual and add the first Measures. Then Copy this Visual, change the Measure and align them within the space available on your Report Page so that they look all…

In reporting, historisation is everything. But besides the usual time series data, we need to look at the historisation of Dimension tables. Here’s how we can do it.Photo by Towfiqu barbhuiya on UnsplashThe most common reporting axis is time.Either on the granularity of days, months, or years.It is crucial that we can see the past without any distortions.One factor in this topic is the “usual” time series with values over time.But the other part of a reporting solution is the dimensions.What happens when something…