Press "Enter" to skip to content

Category: DAX

Changing Visual Titles on Filter with DAX

Published 2023-06-16 by Kevin Feasel

Allison Kennedy has a new title:

Today’s post is a short one with a video to demonstrate what I have learned. I’m a big believer in providing your end user with as much information as possible. Someone new should be able to pick up the report and understand it without needing to get a degree in Power BI or go digging through your company’s intranet to learn what’s what. 

One of the neat features that Power BI provides, is the ability for any visual to act as a filter or ‘cross-filter’ to other visuals. This provides us with comparative values and highlighted bars, but Power BI tooltip just says ‘highlighted’, which isn’t very helpful.

Click through for a bit of DAX code which can help with that, as well as a video demonstrating how it all comes together.

Comments closed

Calculating Moving Averages with DAX

Published 2023-06-13 by Kevin Feasel

Leila Etaati smooths the curve:

The calculation will be done for all rows, till we don’t have the anymore data ahead ( 3 rows ahead) as you can see it will finished on row 2018 as 2018 is the average of 2018, 2019 and 2020.

so in the moving average we will miss some data point but replacing with average of data.

Less data point but help us to see a clear trend.

Click through to see how to do this. Moving averages is really good for the reports in which you want to get a rough magnitude but don’t care about exact values and don’t want oscillations to throw you off. For example, if I need to know how many seats to reserve for an event, I might use a moving average of the last several events as my baseline, rather than the prior event’s number.

Comments closed

Performing Linear Regression in Power BI

Published 2023-06-06 by Kevin Feasel

Marco Russo and Alberto Ferrari build a regression:

LINEST and LINESTX are two DAX functions that calculate a linear regression by using the Least Squares method. Both functions return multiple values, represented in a table that has a single row and one column for each of the values returned.

LINEST gets column references as arguments, whereas LINESTX explicitly iterates over the table provided in the first argument and executes the other arguments in a row context. Internally, LINEST invokes LINESTX and provides to it the table that contains the column references specified in the LINEST arguments. This article describes the more generic function LINESTX.

This is pretty neat for adding a visual element, though I’d probably include an R or Python visual and do the regression in there, myself.

Comments closed

MATCHBY for DAX Window Functions

Published 2023-05-26 by Kevin Feasel

Jeffrey Wang plays matchmaker:

In this final installment of our four-part mini-series on DAX window functions, we’ll be focusing on a new development. The May release of Power BI Desktop has enriched all DAX window functions – namely OFFSET, WINDOW, INDEX, RANK, and ROWNUMBER – with an additional sub-function, MATCHBY, supplementing the existing sub-functions, ORDERBY and PARTITIONBY. In this article, we’ll delve into the purpose of the MATCHBY function, along with the three specific problems it aims to resolve.

Read on to understand what the MATCHBY sub-function does and why it can be important.

Comments closed

ALL and ALLSelected in DAX

Published 2023-05-19 by Kevin Feasel

Reza Rad compares and contrasts:

These two functions in DAX are often used instead of each other; ALL and ALLSELECTED. Their behavior can be similar in some contexts, but it can also be different in other contexts. In this article and video, I’ll explain the difference between these two functions and when to use each in DAX for Power BI, Analysis Services, or Power Pivot.

Reza has a video as well as a blog post to describe the differences.

Comments closed

Optimizing Text Search in DAX

Published 2023-05-09 by Kevin Feasel

Marco Russo and Alberto Ferrari prime the pump:

When you import a table in Power BI, all the strings contained in a text column are stored in a dictionary, which improves the compression and provides excellent query performance when there is a filter with an exact match for the column value. However, reports that apply complex filters on a text column may have performance issues when the dictionary has a large number of values: depending on many other variables, a column with a few thousand unique values might already present a bottleneck, and this is definitely an issue when there are hundreds of thousands of unique strings in a column.

In October 2022, there was an internal optimization in Power BI that has improved the performance of these searches by creating an internal index. Chris Webb described this optimization in his article, Text search performance in Power BI. In this article, we explore how to evaluate whether the optimization is applied and how to measure any performance improvements. As usual, everything comes at a price: creating the index has a cost, that you will see applied to the first query hitting the column. We will also see how to detect this event and the existing limitations for this optimization.

Click through for their deep dive into the process. The final answer reminds me of the warehousing world, where you might pre-run some important queries to get those pages into the buffer pool and available for later reports.

Comments closed

Rounding Errors by Data Type in DAX

Published 2023-04-25 by Kevin Feasel

Marco Russo and Alberto Ferrari shave of fractions of a cent:

The first reason to choose a data type is the range of numbers supported and the precision. However, the result of a mathematical operation may produce a number that cannot be represented in the chosen data type, which requires a rounding operation. Therefore, the result of one same sequence of operations can produce different results depending on the data type and the order of execution. In this article, we discuss the typical rounding behavior for each data type and how to avoid possible issues in your DAX formulas because of any differences from the results you may have expected.

Read on to learn what granularity limits exist for integers, fixed decimal numbers, and floating point operations.

Comments closed

DAX Updates: RANK and ROWNUMBER

Published 2023-04-18 by Kevin Feasel

Jeffrey Wang takes us through some DAX language updates:

In the April 2023 release of Power BI Desktop, two new functions, RANK and ROWNUMBER, have been added to the DAX window functions family, along with significant enhancements to the ORDERBY sub-function. These improvements allow ORDERBY to support sorting by arbitrary DAX scalar expressions, rather than being limited to column names. This not only benefits the new functions, but also existing window functions that we have previously discussed here and here. You might be wondering, since we have had RANKX and RANK.EQ since the inception of DAX, why do we need another rank function? In today’s blog, we will address this question and explore other considerations related to using these new functionalities.

Click through for examples and caveats.

Comments closed

Performing a Pareto Calculation in DAX

Published 2023-04-17 by Kevin Feasel

Phil Seamark does some manufacturing analysis:

I always enjoy it when we get new DAX functions, especially so for the new set of WINDOW Functions recently added. As part of the April 2023 release of Power BI Desktop, we now have a RANK function and the ability to use a measure to control the order within the existing WINDOW function.

The first thing that sprung to my mind was to see how a Pareto calculation might leverage the new capability.

The basic idea of a Pareto calculation is to create a curve like representation of data ordered from largest to smallest.

Read on to see how.

2 Comments