Category: DAX

Matt Allington has a workaround for us:

DAX is a tricky language; on the surface it is deceptively simple, but under the hood it can quickly become complex and it can take many years to master. If you have ever typed a formula and crossed your fingers when you press Enter, then you know what I mean. If you are reading this article, you are no doubt already on your own DAX learning journey.

As you become more competent at DAX, you will start to write more complex formulas that behave differently depending on the filters in your visuals.  One such example is the P&L report that I shared in 2020.  This report contains quite a tricky formula. It was built slowly and methodically, one step at a time, until it was working as desired.  This is the best way to write DAX.  If you want to see how I do it, then go back to the article linked above and watch the video.  While I recommend this as the best approach to writing formulas, the reality is that sometimes you will need to go back to a formula you wrote previously and debug that formula.  Maybe it is not working correctly, or maybe you need to enhance it for some reason.  Whatever the reason, today I am sharing with you my technique to debug complex formulas using tool tips.

Read the whole thing.

Marco Russo and Alberto Ferrari show off some new DAX syntax:

Introduced in December 2020, the DEFINE COLUMN statement lets you define a calculated column local to a query. The column is not persisted in the model, it exists only for the lifetime of the query. Apart from that, it is a calculated column in every sense of the term.

The extension of DAX with the capability to define calculated columns local to a query is needed in order to support composite models over Analysis Services (AS). There are no limitations in the use of the feature. For this reason, you can take advantage of local columns in any DAX query. We refer to calculated columns defined in a query as query calculated columns, or query columns for short.

Click through to see it in action. I like this idea a lot, though do read their note regarding performance, contrasting it with ADDCOLUMNS.

Marco Russo and Alberto Ferrari cover a pain point in Power BI:

The VisibleProducts variable contains a list of products for the selection currently displayed in the visual. In the example, we have the top 3 products for each Product Category included in our report. The ranking that is returned is only up to the value selected in the TopN parameter – for this reason, we can use the result of Ranking by Sales to filter the visual, including only the products ranked in the 1-to-TopN Value range. We use a filter in the Power BI filter pane to accomplish this task.

This is a common enough pattern that I do wish Power BI made it easy.

Stephanie Bruno embraces the healing power of AND:

When using slicers in Power BI reports, multiple selections filter data with OR logic. For example, if you have a slicer with products and your visuals are displaying total number of invoices, then when “bicycles” and “helmets” are selected in the products slicer your visual will show the number of invoices that include bicycles OR helmets. But what if you need to have it instead only show the number of invoices that include bicycles AND helmets? Read on to find out how you can do just that with DAX.

Read on for the solution.

Alberto Ferrari walks us through ranking based on multiple columns:

DAX offers the RANKX function to compute ranking over a table, based on measures or columns. One limitation of RANKX is that it is only capable of ranking using a single expression. Oftentimes it is necessary to use multiple columns to obtain a ranking, either because the business requirement dictates it, or because you want to rank ties with different criteria.

As a demonstration, we rank customers based on their purchase volume. To artificially introduce ties, we use the Rounded Sales measure, that rounds the sales amount to the nearest multiple of one thousand. Using Rounded Sales, several customers show the same amount of 10,000.00. Because they are ties, their ranking must now be defined by alphabetical order based on their names.

Read on for two methods to solve this problem.

Phil Seamark has a new tool for us:

Last week I was honoured to take part in the latest edition of the Power BI Dev Camp which is run by my colleague Ted Patterson. It was a fun session which I enjoyed.

As part of the Dev camp, I walked through some of my recent Visual Studio Code based blog posts on how to perform various tasks against models hosted in Power BI desktop.

While preparing for the session, Ted and I agreed that it might be helpful to create a small external tool that could automatically format all DAX expressions in a Power BI model. The idea is to leverage the excellent DAX Formatter API provided by the good folks at SQLBI. This API is the same endpoint used when you format your DAX using DAX Studio.

Read on for more details.

Gilbert Quevauvilliers walks us through the Vertipack Analyzer in DAX Studio:

If you are looking to better understand your Power BI Model, how big are your tables, which column is taking up the most space then you can use Vertipaq analyzer which is built directly into the amazing DAX studio.

If you are looking for a video on how to do this, there are some awesome videos found here.

SQLBI.COM – Introducing VertiPaq Analyzer in DAX Studio

Guy In a Cube – How do you even use VertiPaq Analyzer with Power BI???

The reason for my blog post, is that I find sometimes I want to watch a video to better understand the content, and other times I want to follow a step by step process on how complete the analysis.

Click through for a walkthrough of the process.