Press "Enter" to skip to content

Category: DAX

One-Column Fusion with DAX

Published 2020-06-12 by Kevin Feasel

Phil Seamark has a performance tuning tip for DAX:

I wrote an article about an optimisation called DAX Fusion that attempts to fuse similar SE calls when it can. This article highlights an elegant DAX-based trick that works for a specific scenario by reducing the number of SE calls that doesn’t rely on DAX fusion. The difference between 1-Column fusion and the other is:

– DAX fusion in the engine works across multiple columns that have the same effective WHERE clause
– 1-Column fusion works by fusing multiple measures that all reference a single column, but with different WHERE clauses

Read on to learn how to switch around a bit of DAX to reduce the number of storage engine calls, as well as an example of one scenario in which it can come in handy.

Comments closed

Returning Multiple Values in Power BI with ConcatenateX

Published 2020-06-10 by Kevin Feasel

Nick Edwards shows how you can use the ConcatenateX DAX function to combine values:

In this blog post we’ll take a quick look at using ConcatenateX function to view a concatenated string of dates where the max daily sales occurred for a given month.

I came across this function whilst going through the excellent “Mastering DAX 2nd Edition Video Course” by the guys from SQLBI.com. So credit to Marco and Alberto for sharing this.

So how does it work? If we had a list of dates ranging from 01/01/2020 to 31/12/2020 and we wanted to see which days we achieved maximum sales for each given month in a year we could use the ConcatenateX function to return these dates in a single row per month.

Click through for the demo.

Comments closed

Expression-Based Formatting in DAX

Published 2020-05-18 by Kevin Feasel

Matt Allington points out a new look to a slightly less new feature:

When I say “new”, they are actually not new – this feature has actually been around since the start of 2019. What is “new” is the discoverability of the feature. Prior to the April 2020 release, you had to first hover your mouse button over the section (Title text in the example above). After you hovered your mouse, you would see a vertical … menu (kind of like a vertical ellipsis), then when you hovered over the vertical ellipsis, you would then see the Fx button. Click the mysterious hidden button and only then could you discover the world of expression based formatting.

Click through to learn about expression-based formatting and where it might be useful.

Comments closed

Getting a Substring with DAX

Published 2020-04-07 by Kevin Feasel

Reza Rad shows us how to build out a substring using DAX:

Substring is one of the most common functions in many languages, However, there is no function named Substring DAX. There is a very simple way of doing it, which I am going to explain in this post. Substring means getting part of a string, for example from “Reza Rad”, if I want to get the start starting from index 2, for 4 characters, it should return “za R”. Considering that the first character is index 0. Let’s see how this is possible.

The answer’s not as pretty as a SUBSTRING() function would be, but it’s also not too far off.

Comments closed

Obtaining Accurate Totals in DAX

Published 2020-04-02 by Kevin Feasel

Alberto Ferrari explains a nuance of summation in DAX:

In simple DAX measures, the total of a report is the sum of its individual rows. For more sophisticated measures, the total might seem wrong because the formula does not include an aggregation over the rows that are visible in the report. For example, if the total of a measure must be the sum of the values displayed in the rows of a report, we consider the expected result a “visual total”, which is a total that corresponds to the visual aggregation of its values sliced by different rows in the report.

Click through for a straightforward demonstration.

Comments closed

Using DAX’s GroupBy Function

Published 2020-03-30 by Kevin Feasel

Reza Rad takes us through the GroupBy function in DAX:

There are many different ways you can create aggregations in Power BI, You can do it in the source (using the database t-SQL language), or using Group By operation in Power Query. You can also do it in DAX using some functions. One of the functions that can be used for grouping and aggregation is Group By. This article is about how to use Group By in DAX. Creating aggregation using DAX is a very useful skill because you can use it to create virtual tables in your measures and have better dynamic calculations in Power BI.

Click through for the explanation and an example.

Comments closed

The KEEPFILTERS Function in DAX

Published 2020-03-25 by Kevin Feasel

Phil Seamark explains what the KEEPFILTERS() function does in DAX:

If you ever spend time looking at DAX generated by the Power BI Desktop Performance Analyser, you may notice a function called KEEPFILTERS appear from time to time. It’s not a function I find myself using often, so I thought I’d do a bit of digging to find out what it does and when it might be useful.

Read on to learn exactly what it does.

Comments closed

Finding Active Users by Date with DAX

Published 2020-03-16 by Kevin Feasel

Reza Rad takes us through the subscription pattern:

One of the common patterns of a data model is the subscription pattern. In this pattern, we have subscriptions (or tickets, or issues, or whatever else you want to call it) open date and close date. The way that this data is stored in a table makes it a bit challenging to get informative insight out of it. In this post (first of the series), I am going to explain about the subscription pattern and one of the common calculations needed for it; which is active subscribers at any given date, or open tickets at any given date.

In case you’re curious, here’s a solution which works in T-SQL. I’ve really taken to event-style tables, where there’s one row per state change, so instead of having a begin date and an end date for each action, have a row which contains the date and the type of action. This makes operating on the data a lot easier, though it does make rules preventing common entry problems (end date before start date, etc.) a bit trickier.

Comments closed

Approximate Distinct Count with DAX

Published 2020-03-04 by Kevin Feasel

Gilbert Quevauvilliers runs some performance tests against the approximate distinct count formula in DAX:

I am currently running SQL Server Analysis Services (SSAS) 2019 Enterprise Edition. (This can also be applied to Power BI)

My Fact table has got roughly 950 Million rows stored in

And as mentioned previously it has got over 64 Million distinct users.

The data is queried from SQL Server into SSAS.

Gilbert first checks how close these are and then how much faster the approximate count is.

Comments closed