Press "Enter" to skip to content

Category: DAX

INT vs CONVERT in DAX

Published 2022-02-15 by Kevin Feasel

Marco Russo contrasts two functions for us:

Writing explicit type conversions is unusual in DAX, because most of the time the implicit conversion happening between different data types in an arithmetic expression provides the results you wanted. However, you might want to enforce a type conversion for different reasons: to round a number or to make sure a certain calculation is always approximated the same way. In particular, the conversion to an integer number can be obtained using different techniques – sometimes with small differences which in borderline cases might produce different results.

What we examine in this article is the difference between two techniques to convert a number to an integer: INT and CONVERT. 

Read on to see the differences, including where things break down for INT.

Comments closed

Guidance on SUMMARIZE() and ADDCOLUMNS() in DAX

Published 2022-02-14 by Kevin Feasel

Marco Russo has some advice for us:

Everyone using DAX is probably used to SQL query language. Because of the similarities between Tabular data modeling and relational data modeling, there is the expectation that you can perform the same operations as those allowed in SQL. However, in its current implementation DAX does not permit all the operations that you can perform in SQL. This article describes how to use ADDCOLUMNS and SUMMARIZE, which can be used in any DAX expression, including measures. For DAX queries, you should consider using SUMMARIZECOLUMNS, starting with the Introducing SUMMARIZECOLUMNS article. You can also read the All the secrets of Summarize article for more insights about inner workings of SUMMARIZE.

Read on to see how it all works.

Comments closed

Macros in Tabular Editor 3

Published 2022-01-06 by Kevin Feasel

Matt Allington notes a key feature in Tabulor Editor 3:

Today I am talking about Macros in Tabular Editor 3. This is a new name for an old feature. In Tabular Editor 2, this feature is called Advanced Scripting (a term I actually prefer, but oh well).  I think one reason for the name change is there are now multiple types of scripting, including the new DAX scripting feature (I covered that as a key feature I love in the article linked above).

Click through to see how it works. Tabular Editor 3 is a paid product, though the free Tabular Editor 2 is still around if your employer won’t front the cash for 3.

Comments closed

Get the Previous Non-NULL Value in DAX

Published 2021-12-15 by Kevin Feasel

Kristyna Hughes calculates the lagged value in DAX:

If you are an avid report designer or user, you may have wanted to see the percent difference between one row and the previous one. Sounds easy right? Well let’s break it down. Percent difference follows a consistent formula: (current value – previous value)/previous value. Current value? Easy peasy. Previous value? That’s where this blog comes into play. It’s fairly simple to get the previous month value or previous date period value because Power BI can tell it exactly what the filter would be (September minus one month is always August ). However, there may not have been any invoices in August, so how can we compare September with the last month that had an invoice?

Click through to see how.

Comments closed

Using ConcatenateX in Power BI

Published 2021-11-15 by Kevin Feasel

Reza Rad describes a DAX function:

It happens often in Power BI calculations and reports that you need to concatenate a list of values from a column. You can do this concatenation in Power Query or DAX. However, if the concatenation needs to be done dynamically. ConcatenateX is a very helpful DAX function to achieve such results. It is very helpful to understand what happens in the virtual tables in DAX too. In this article and video, I’ll explain what ConcatenateX is and how it works in Power BI and DAX.

Click through for a video, as well as a detailed explanation in blog post format.

Comments closed

Using the Power BI REST API for DAX Queries

Published 2021-10-08 by Kevin Feasel

Gilbert Quevauvilliers writes some Powershell:

In this blog post I am going to show you how to use PowerShell to run a DAX query from my dataset, and then store the results in a CSV file.

I will also include the PowerShell code!

I really liked the awesome blog post by Kay on the Power BI Team which you can find here: Announcing the public preview of Power BI REST API support for DAX Queries

Read on to see what prep work you need to do, as well as the scripts needed to pull this off.

Comments closed

Measure Filters in Power BI

Published 2021-09-14 by Kevin Feasel

Marco Russo and Alberto Ferrari dive into a topic:

The first paragraph of this article needs to be a warning: the article itself is here for DAX and Power BI enthusiasts only. We are going to show a report that does not work, and then we explore how to fix the problem by performing a deep analysis of the queries generated by Power BI, finding the problem, and finally fixing it. The article contains a lot of references to advanced DAX concepts and the final solution is NOT a best practice. The value of the article is not in the specific solution. Rather, the important part is that a deep understanding of DAX and Power BI can help you obtain the right results, specifically when you have the feeling that you are faced with a bug because Power BI is acting strange. If you do not like DAX before reading this article, you will like it even less at the end. But if you love DAX, then chances are you will really enjoy the reading, even though it requires quite a lot of brain bandwidth. For sure, it took all of mine when I first encountered this behavior.

Break out the propeller hats before you dive in.

Comments closed

Creating and Using Variables in DAX

Published 2021-09-13 by Kevin Feasel

Jeet Kainth takes us through the process of working with variables in DAX:

Variables can simplify your DAX code, help with debugging and help improve performance. To use variables in your DAX measure you need to declare a variable using the VAR keyword, give the variable a name, and then assign an expression to the variable. You can use multiple variables in a measure but when using variables you must use a RETURN statement to specify the final output to be returned.

Read on for a demonstration, as well as several examples of how you can use variables to make your DAX-writing life easier.

Comments closed

The CONTAINS Function in DAX

Published 2021-08-17 by Kevin Feasel

Marco Russo and Alberto Ferrari share their thoughts on the CONTAINS() function in DAX:

The CONTAINS function in DAX has been available since the very first version of the language in 2010. In the evolution of the language, new syntaxes and functions have been added, and several use cases for CONTAINS that were valid many years ago are no longer considered good practice. The goal of this article is to clarify when CONTAINS is a good practice and when there are better alternatives to solve common problems.

Read on for that explanation.

Comments closed