Press "Enter" to skip to content

Category: DAX

Data Types and Arithmetical Calculations in DAX

Published 2024-10-09 by Kevin Feasel

Marco Russo and Alberto Ferrari do some counting:

The VertiPaq engine is basically data type-independent. This means that it does not matter whether a column is a string, a floating point, or a date: because of the dictionary encoding happening inside VertiPaq, all these data types use around the same amount of memory and perform at nearly the same speed.

However, when mixing different data types in the same expression, DAX will likely need to perform conversions between data types. Some of these conversions are nearly free, whereas others require the intervention of the formula engine, with a related performance impact.

We have already written about possible errors occurring during data type conversion here: Understanding numeric data type conversions in DAX and here: Rounding errors with different data types in DAX. The issue with conversion errors is mostly due to the fact that the precisions of fixed decimals (also known as Currency) and decimals (also known as floating point) are different. This article starts with a focus on performance.

Read on to see what Marco and Alberto have for us this time.

Leave a Comment

Debugging a DAX Measure with DAX Query View

Published 2024-09-26 by Kevin Feasel

Dennes Torres sorts out an issue:

DAX Query View was created some time ago and it was a great tool to run DAX inside Power BI environment.

However, testing a measure in DAX Query View may not be something so obvious. While the measure in a report obeys to the filter context and most of times results in a single value, the filter context doesn’t exist in Query View and the execution needs to return a table.

In this way, a simple copy/past of the measure in DAX Query View will not run. The measure needs to be adapted to be tested.

Let’s analyze one sample case to discover how this happens.

Click through for the answer.

Leave a Comment

Creating Custom Visuals in Power BI with DAX

Published 2024-09-24 by Kevin Feasel

Kurt Buhler shows one way to customize Power BI visuals:

When creating reports in Power BI, you regularly come across scenarios where you want to show data in a certain way that is not straightforward in the standard “core visuals”. Sometimes, the visualization needs to reflect particularities about an organization’s business processes or its data. Other times, you simply want a more creative design to visualize the data in the most effective way. In this article, we discuss what to do in these scenarios, describing one method where you can make custom visualizations by using a single DAX measure, and the caveats and limitations of this particular approach.

In this article, our objective is to produce a matrix visual with the Timeline SVG, like in the following diagram.

Read on for several methods to handle when Power BI doesn’t do something out of the box, and a focus on creating a custom visual via DAX measure with an SVG. But do read the whole thing, as Kurt explains why this isn’t necessarily a great method.

Leave a Comment

Context Transition in DAX

Published 2024-09-11 by Kevin Feasel

Marco Russo and Alberto Ferrari draw on a cocktail napkin:

In previous articles, we introduced a visual approach to describing two important DAX concepts: the filter context and the row context. This article completes this short series by describing the context transition using a graphical visualization.

This article provides a different perspective on the context transition already covered in other articles: you should read them to get more insights on this important concept for DAX.

Read on to see how it all fits together.

Comments closed

Displaying Dates without Data in Power BI

Published 2024-08-29 by Kevin Feasel

Alon Ohayon looks for the missing month:

When you’re using Power BI, you probably create line charts that show data by month pretty often. It usually works great, but what happens if some months don’t have any data?

By default, Power BI just skips those months in the chart. That might seem okay, but it can actually be misleading—especially if you’re looking for trends over time, including the months with no activity.

Alon shows us an example of how to do this in DAX and that works. But if you can solve this at the data layer, such as when querying from a SQL Server, that’s even better. This happens to be one of the good uses of a calendar table: giving you a complete set of months (or whatever time period you want) that you can then use to left join to your data, returning either a data point with a value, or a NULL that you can coalesce with 0 to ensure that you have a result for each month.

Comments closed

The Importance of Filtering Columns instead of Tables in DAX

Published 2024-08-29 by Kevin Feasel

Marco Russo and Alberto Ferrari explain it to us:

You have probably heard multiple times the same answer to many questions about DAX and, to be honest, about nearly anything IT-related: “it depends”. Is it fine to create a calculated column, or is it better to avoid it? Yes, no… it depends. Is it better to create one, two, or three fact tables? It depends. Is a composite model the right choice? It depends. However, a few questions have a strong, clear answer, and in this article, we focus on one of those. Is it better to filter a table or a column with CALCULATE? Here, the answer is simple and definite: filter columns, not tables. The same principle is applied to CALCULATETABLE, even though the example in this article only shows CALCULATE.

With that answer in mind, read on for the wherefore.

Comments closed

A Visual Explanation of Row Context in DAX

Published 2024-07-30 by Kevin Feasel

Marco Russo and Alberto Ferrari get visual:

Row context is the second fundamental concept in writing DAX code. In a previous article, we introduced the first concept – the filter context – using a visual approach. In this article, we rely on graphical visualization to describe a row context.

This article provides a different perspective on a topic already discussed in other row context articles: read them to get more insights about this important concept for DAX.

Click through for a great primer on the topic.

Comments closed

Variable Evaluation Time in DAX

Published 2024-07-16 by Kevin Feasel

Marco Russo and Alberto Ferrari evaluate some variables:

Variables are an important element of DAX to improve readability and performance. Variables are created through the VAR keyword, which can be repeated multiple times for multiple variables, followed by RETURN, the keyword that defines the result of the expression.

Read on for several examples of how you can use variables, and even if it’s possible for the engine to ignore variable declarations if the variable in question never sees use.

Comments closed

Window and Information Functions in DAX

Published 2024-07-09 by Kevin Feasel

Nikola Ilic continues a series on getting ready for the DP-600 exam:

If you’re coming from the SQL world, you might have already heard about window functions. However, window functions are relatively new enhancement in the DAX language. Similar to SQL, they aim to provide the possibility to calculate specific expressions over a sorted and partitioned set of rows.

Read on to learn about three window functions in DAX, followed by a discussion of information functions and what they do.

Comments closed

Antipattern: DAX Measures Never Returning Blank

Published 2024-07-08 by Kevin Feasel

Chris Webb explains the value of BLANK:

Following on from my earlier post on the Query Memory Limit in Power BI, and as companion to last week’s post on how a DAX antipattern using Calculate() and Filter() can lead to excessive memory consumption by queries (and therefore lead to you hitting the Query Memory Limit), in this post I want to look at the effects of another DAX antipattern on performance and memory usage: measures that can never return a blank value.

Read on to see how much of a difference using DAX to fill a grid with 0’s can make.

Comments closed