Category: DAX

Marco Russo and Alberto Ferrari observe the dog that didn’t bark:

What products did not sell in a specific area, store, or time period? This may be an important analysis for several businesses. There are multiple ways to obtain the desired result. Some specific implementations might be needed because of the user or model requirements, whereas developers can choose any formula in several cases. Or you might just find a solution on the web and blindly implement it without questioning whether there is a better way to achieve what you want.

It turns out that different formulas perform very differently. Choosing the right one in your scenario can make a slow report fast. This article analyzes the performance of different formulations of one same algorithm. 

It’s interesting to see the performance profile here: most are reasonably close together, although you can still get a 2x gain from using the fastest approach versus the second-slowest. And then there’s the slowpoke.

Marco Russo and Alberto Ferrrari control the horizontal, Marco Russo and Alberto Ferrari control the vertical:

DAX is a powerful tool in the hands of a Power BI developer. Using simple DAX formulas, you can not only compute interesting metrics but also customize the behavior of Power BI visuals. In this article, we use DAX to control the range of charts to obtain more coherent visualizations.

Read on to see how.

Nikola Ilic takes a look:

A few months ago, while scrolling through posts on social media, one of them immediately grabbed my attention! It was about a new tool, called DAX Optimizer, which promised to identify and remove performance bottlenecks in your DAX formulas. For all of us dealing with optimizing Power BI reports on a day-to-day basis, that was a huge promise (and one I was impatiently waiting to see in action).

One important note is that this is not a free tool, as Nikola mentions. Read on for more thoughts about how it works, what it picks up, and whether it’s a good fit for your environment given the price.

Marco Russo and Alberto Ferrari make a comparison:

DAX offers a rich set of functions, some of which overlap in their functionalities. Among the many, two functions perform grouping: SUMMARIZE and GROUPBY. These are not the only two: SUMMARIZECOLUMNS and GROUPCROSSAPPLY perform similar operations. However, the article is about SUMMARIZE and GROUPBY, as the other functions have many more functionalities, so a comparison would be unfair.

To make a long story short: GROUPBY should be used to group by local columns, columns created on the fly by DAX functions. SUMMARIZE should be used to group by model and query columns. Be mindful that both functions support both scenarios: both functions can group by model and local columns. However, using the wrong function translates into a strong decrease in performance.

Read on for a detailed explanation.

Gilbert Quevauvilliers pulls back the curtain:

I was once again working with the grouping feature in Power BI Desktop.

This time I had a lot of values that I wanted to put into multiple groups. I also did not want to select the individual values (I have previously shown this in my blog post Creating a group with all values (Even if not in the data) in Power BI)

It is amazing that sometimes it takes me a while to fully understand how to use some features in Power BI and today was one of those days!

Click through to see what Gilbert learned and how you can use that information to group by ranges instead of individual values.

Marco Russo and Alberto Ferrari do some slicing and filtering:

Slicers with too many values might be inconvenient for users, as they must search for the desired selection among too many lines. In such cases, a common solution is to build a hierarchy and use slicers with multiple columns inside, or multiple slicers, each with one column. However, this solution works only in structures with a natural hierarchy, like continents and countries. Indeed, each country belongs to only one continent so the hierarchy can be easily created with a new column.

If the hierarchy is non-natural, the relationship between the parent and the children is many-to-many, requiring a specific type of relationship. 

Click through to see what that relationship looks like and how you can build it.

Gilbert Quevauvilliers builds a data validation tool in Excel:

I had a requirement where the customer wanted to make sure that the right user could only see the colour from the stock item assigned to them. Essentially using the Excel file for Row Level Security.

For the Row level security to work correctly it required the colour name to be an exact match.

For me to ensure that this would happen, I wanted to make sure I could get the data from my Power BI dataset, and then using the Excel data validation make sure that the user could either type in the value 100% correct or pick it from the drop-down list.

There are quite a few steps, but I can see business people being happy about the final outcome.

Marco Russo and Alberto Ferrari break the ties:

DAX already offered two ranking functions so far: RANK.EQ and RANKX. While RANK.EQ is very seldom used, RANKX has been the primary ranking function for a long time. We wrote several articles about RANKX: one of them deals with ranking on multiple columns, a scenario that requires some DAX acrobatics to be solved as described in RANKX on multiple columns with DAX and Power BI.

The new RANK function makes ranking on multiple columns much easier because it offers sorting by multiple columns as a native feature, because it belongs to the family of window functions. Thanks to the concept of current row in window functions, RANK also helps in a subtle issue with RANKX – which despite being rare, might affect ranking on decimal numbers as described in Use of RANKX with decimal numbers in DAX.

Read on to see how you can use RANK() and how it compares to RANKX().