Press "Enter" to skip to content

Category: DAX

An Overview of DAX Visual Calculations

Published 2024-02-29 by Kevin Feasel

Teo Lachev takes us through visual calculations in DAX:

Suppose you need a measure that calculates the difference between the product categories in the order they were sorted in the visual. Implementing this as a regular DAX measure is a challenge. Yet, if we had a way to work with the cells in the visual, we can easily find a way to get this to work. Ideally, this would work similar in Excel, but DAX doesn’t know a think about cell references. However, visual calculations do.

As always, Teo gives us a view of the good, the bad, and the ugly with this feature.

Comments closed

Row-Level Security and USERELATIONSHIP() with Inactive Relationships

Published 2024-02-27 by Kevin Feasel

Marco Russo and Alberto Ferrari have a public service announcement:

USERELATIONSHIP is a very common and helpful function, used whenever there are multiple relationships between tables and developers need to decide which relationship to use. However, in some scenarios, this common function raises an annoying error:

The UseRelationship() and CrossFilter() functions may not be used when querying ‘Sales’ because it is constrained by row-level security.

As with all the error messages, this requires some understanding and further explanation. Moreover, a workaround is straightforward to find. However, the workaround has some subtle restrictions that need to be well understood.

Read on to learn more.

Comments closed

Avoiding Time Intelligence DAX Functions in DirectQuery Mode

Published 2024-02-16 by Kevin Feasel

Marco Russo and Alberto Ferrari skip the slow stuff:

Calculations that use the DAX time intelligence functions mostly retrieve data at the day level, performing the required aggregations in the formula engine. By avoiding time intelligence DAX functions, you can force DAX to produce more optimized queries for your specific calculations.

DirectQuery over SQL and VertiPaq require the same patterns to optimize time intelligence calculations, even though the reasons are different. In VertiPaq, we try to stay away from DAX time intelligence functions to avoid large materialization at the day level. With SQL, materialization does not always happen because Tabular tries to push the grouping down to SQL. Still, time intelligence calculations often result in complex queries, and it is better to avoid the complexity by using simpler DAX code.

Check out the performance difference.

2 Comments

Viewing DAX in Microsoft Fabric with SemPy

Published 2024-02-15 by Kevin Feasel

Kevin Chant talks about a recent issue:

Recently I have been helping others get up to speed with Microsoft Fabric. Which includes going through some Power BI topics.

One issue that came up was how to show them the DAX used for a measure within a Power BI report that had been published to Microsoft Fabric. To link working with measures in Power BI Desktop with working in Microsoft Fabric.

Kevin shows the normal way of doing this, as well as an alternative using the SemPy library.

Comments closed

Tuple Syntax in DAX Expressions

Published 2024-01-16 by Kevin Feasel

Marco Russo and Alberto Ferrari build tuples:

The DAX language has a tuple syntax commonly used in table constructors. However, the tuple syntax can also be used whenever you want to describe the combined values for two or more columns – this can be handy when you test the combined values of multiple columns, such as year and month. After reviewing the table constructor syntax, we introduce the tuple syntax and a few examples of where tuples can simplify the DAX code, making it more efficient and easier to read.

Half of the difficulty of working with tuples is pronouncing “tuples.” It’s pronounced “tuples,” not “tuples.”

Comments closed

Switching between Active Relationships in Power BI Models

Published 2024-01-10 by Kevin Feasel

Meagan Longoria solves a head-scratcher:

A couple of weeks ago, I encountered a DAX question that I had not previously considered. They had a situation where there were two paths between two tables: on direct between a fact and dimension and another that went through a different dimension and a bridge table.

Click through for several examples of when this might come up, as well as how to solve the problem.

Comments closed

Joining Tables without Relationships in DAX

Published 2024-01-02 by Kevin Feasel

Marco Russo and Alberto Ferrari have ended a relationship:

In a previous article, we saw several examples of using the NATURALLEFTOUTERJOIN and NATURALINNERJOIN functions in DAX. In that article, we saw how to join tables in the data model using existing relationships. However, if we want to join tables in the model without using the relationships in the model, we must eliminate the data lineage of the columns to use in the join condition. This article describes how to achieve this.

I suppose my question here is, when would you want to do this? I’m sure there’s a good reason to, but I’m just as sure that I don’t know it.

Comments closed

DAX in Review: 2023

Published 2023-12-29 by Kevin Feasel

Marco Russo gives us a review:

Despite the large number driven by the new INFO functions, we did not see big changes in DAX, but rather a consolidation of the new window functions.

After three years, in 2023 the composite models reached the general availability! More important, the official name is now “composite models”, dropping the previous “DirectQuery for Power BI datasets and Analysis Services” name used in preview. Add to this that “datasets” have been renamed to “semantic models” (a change we fully support at SQLBI), and it seems this year we made peace with the Microsoft naming departments (there should be more than one).

Click through for more on DAX, as well as what Marco and Alberto have been up to this year.

Comments closed

Join Functions in DAX

Published 2023-12-19 by Kevin Feasel

Marco Russo and Alberto Ferrari join datasets together:

Readers with knowledge of SQL know that the join operation is widespread in SQL queries, as it is the standard way to combine data stored in different tables. It is however uncommon to explicitly join tables in DAX because the relationships in the data model provide enough information to allow many DAX functions to work without an explicit join operation. Most of the time, the join between tables is implicit and automatic.

However, DAX has two explicit join functions: NATURALLEFTOUTERJOIN and NATURALINNERJOIN. Apparently, these functions correspond to the behavior of LEFT OUTER JOIN and INNER JOIN in SQL. However, they differ from SQL in how you specify the join condition. This article shows how these functions can be used in DAX with practical examples. If you need a more introductory article about the syntax of these functions, read From SQL to DAX: Joining Tables, where we compare the SQL syntax with similar DAX functions.

Click through to learn more about how these functions work and what their limitations are.

Comments closed