Press "Enter" to skip to content

Category: DAX

Using KEEPFILTERS in DAX

Published 2024-05-07 by Kevin Feasel

Marco Russo and Alberto Ferrari share some advice:

In the article Using KEEPFILTERS in DAX, we described how to use KEEPFILTERS, which is a function that preserves the existing filter on columns affected by a new filter applied by CALCULATE or CALCULATETABLE. We suggest reading that article if you are not familiar with KEEPFILTERS. However, we wanted to clarify a rule of thumb you can apply to quickly decide when to use KEEPFILTERS or not in most cases. This will allow you to only invest more time when you are dealing with specific requirements.

Read on for common use cases of KEEPFILTERS, as well as recommendations on when and how you should use the function.

Comments closed

Filtering a Visual by a Measure via a Slicer in Power BI

Published 2024-05-02 by Kevin Feasel

Meagan Longoria solves a problem:

Have you ever wanted to filter a visual by selecting a range of values for a measure? You may have found that you cannot populate a slicer with a measure. But you can do this another way.

I have a report that shows project expenses and budgets. I want users to be able to filter the list of project to only those which have expenses within my selected range. I also have 2 other slicers for project budget and percent of budget used, but let’s just focus on the expense amount slicer.

Read on to see how.

Comments closed

Comparing Cumulative Values for Events across Different Periods

Published 2024-04-23 by Kevin Feasel

Kurt Buhler performs comparative analytics:

In a previous article about format strings, we showed an example of how format strings can improve visualizations. The visualizations in that article compared the performance of a company’s marketing videos on a streaming platform to the average of all their videos released that year. In this article, we explain how to conduct this analysis yourself in DAX, where you compare series that occur in different periods.

Click through for an explanation, both in video and in blog form.

Comments closed

Creating DAX Measures Directly in the Power BI Service

Published 2024-04-10 by Kevin Feasel

Gilbert Quevauvilliers skips the desktop step:

There have been some updates to Power BI with Visual Calculations, and as of the March 2024 release you can now create and edit visual calculations in the Power BI Service.

Here are details from the blog post: Power BI March 2024 Feature Summary | Visual Calculations Update

I then saw an email which made me think, could I then use the visual calculations to create a DAX measure directly in the Power BI Service on an existing Power BI report.

The answer, it turns out, is yes. Read on to see how.

Comments closed

VISUAL SHAPE and Visual Calculations

Published 2024-03-12 by Kevin Feasel

Marco Russo and Alberto Ferrari fit the square pegs into the square holes:

Visual calculations, introduced as a preview feature with the February 2024 release of Power BI, aim to simplify the creation of calculations tied to a specific visual. Using visual calculations for simple calculations is straightforward.

However, as soon as developers create more complex calculations, they should understand the technical details of visual calculation implementation. This requires understanding the hierarchical structure of the virtual table, the new visual context, the semantics of ROWS and COLUMNS, the behavior of CALCULATE, and the new visual context modifiers EXPAND and COLLAPSE.

In this first article about visual calculations, we introduce VISUAL SHAPE and the basics of visual calculation implementation, leaving the remaining topics to future articles. A complete whitepaper with a detailed explanation of all these topics will be available soon to SQLBI+ subscribers.

Even without a complete whitepaper, this serves as a useful primer on the topic.

Comments closed

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