Press "Enter" to skip to content

Category: DAX

DAX Window Functions and Power BI DirectQuery

Published 2023-01-03 by Kevin Feasel

Chris Webb points out another benefit of DAX window functions:

The new DAX window functions (announced here, more details on Jeffrey Wang’s blog here and here) have generated a lot of excitement already – they are extremely powerful. However one important benefit of using them has not been mentioned so far: they can give you much better performance in DirectQuery mode because they make it more likely that aggregations are used. After all, the fastest DirectQuery datasets are the ones that can use aggregations (ideally Import mode aggregations) as much as possible.

As always, Chris has a demo for us, so check it out.

Comments closed

A Recap on DAX in 2022

Published 2022-12-30 by Kevin Feasel

Marco Russo recaps the year:

Another important event for the DAX world has been the release of DAX Studio 3, with an important restyling of the user interface and user experience.

After two years, the composite models – whose official name is DirectQuery for Power BI datasets and Analysis Services – are still in preview. General availability should probably happen in 2023. In the meantime, the feature has been refined and improved. At SQLBI we also released new content about this topic.

Click through for info on eight new DAX functions, as well as what Marco and Alberto have been doing all year.

Comments closed

Avoiding Problems with DAX Window Functions

Published 2022-12-28 by Kevin Feasel

Jeffrey Wang shares a few tips to avoid issues with window functions in DAX:

Several people had reported running into errors when trying the window functions on fact tables. Let’s look at an example by first adding a calculated table of three columns to the model that is defined by the following DAX expression, and then, add all three columns to a table visual as shown in Figure 1.

Read on for more. This is an area where expected behavior does differ from what you get with window functions in T-SQL.

Comments closed

Window Functions in DAX

Published 2022-12-15 by Kevin Feasel

Jeffrey Wang is speaking my language:

The December 2022 release of Power BI Desktop includes three new DAX functions: OFFSET, INDEX, and WINDOW. They are collectively called window functions because they are closely related to SQL window functions, a powerful feature of the SQL language that allows users to perform calculations on a set of rows that are related to the current row. Because these functions are often used for data analysis, they are sometimes called analytical functions. In contrast, DAX, a language invented specifically for data analysis, had been missing similar functionalities. As a result, users found it hard to write cross-row calculations, such as calculating the difference of the values of a column between two rows or the moving average of the values of a column over a set of rows.

Read on to learn more about how these functions work and how they differ from their SQL Server counterparts.

Comments closed

Solving Common CALCULATE Filter Argument Errors

Published 2022-12-08 by Kevin Feasel

Marco Russo and Alberto Ferrari catalog some errors:

The expression contains columns from multiple tables, but only columns from a single table can be used in a True/False expression that is used as a table filter expression.

This error is seen when the predicate includes column references from more than one table. For example, if we need a measure that returns the sales made to customers living in the same country as the store, we could try to write the following measure:

Read on for several examples and solid guidance on how to resolve these common issues.

Comments closed

Horizontal Fusion in DAX

Published 2022-11-09 by Kevin Feasel

Marco Russo and Alberto Ferrari put on their lab coats:

Fusion is a DAX optimization that reduces the number of storage engine queries when the engine detects that multiple calculations can be merged together in a single query. There are two types of fusions: vertical fusion and horizontal fusion.

Vertical fusion occurs when multiple measures – or calculations in general – need to be computed in the same filter context. For example, the following query requires the calculation of two measures: Sales Amount and Margin:

Read on to see how horizontal fusion differs and when it can be most useful.

Comments closed

Finding the Real Error in a DAX Measure

Published 2022-10-19 by Kevin Feasel

Teo Lachev builds on a prior post:

This blog builds upon my previous “Resolving Tabular Conversion Errors” and applies to Analysis Services in all flavors (Power BI, MD, and Tabular). In the scenario I described in the previous blog, the server at least told us the name of the offending measure in the error description. But sometimes you might not be that lucky. For example, recently I got this error when running a DAX query requesting many measures: “Microsoft OLE DB Provider for Analysis Services.” Hresult: 0x80004005 Description: “MdxScript(Model) (2000, 133) Failed to resolve name ‘SYNTAXERROR’. It is not a valid table, variable, or function name.” All we know is that there is a syntax error in some measure but good luck finding it if you have hundreds of measures in the query and your model. However, the (2000,133) section references the line number and column number in the MDX script (Yeap, MDX even if you use Tabular), so if we can get the script, we might be able to correlate the error.

Read on to see how you can capture that script and get the error. The technique is definitely not intuitive.

Comments closed

Finding DAX Dependencies

Published 2022-10-14 by Kevin Feasel

Chris Webb tries out a DMV:

If you’re monitoring activity in Power BI, for example using Log Analytics, you’ll know that you can capture the DAX queries generated by your published Power BI reports. How do you make sense of them though? You may want to know which tables, columns or measures are being used by a query – maybe so you can work out which ones aren’t being used and can be deleted. I always thought the only way to do this would be to parse the DAX query, which would be incredibly difficult to do. Yesterday, though, Igor Cotruta pointed out on Twitter that there’s a really easy way to do this that I didn’t know about, using the DISCOVER_CALC_DEPENDENCY DMV. I’ve used this DMV before and blogged about it here, but what was new to me is that you can pass a DAX query into the QUERY restriction (it is all documented here). To see how it works let’s look at a simple example.

This looks pretty cool.

Comments closed