Press "Enter" to skip to content

Category: DAX

Power Query FILTER()

Rob Collie takes us through a good use of FILTER() in DAX:

The thing both of those formulas have in common is that they are using a measure in the filter argument of the CALCULATE function.  In both examples here, I’ve highlighted the offending measure in yellow.

CALCULATE([Sightings per Year], [Avg Sighting Length in Mins]>6)

CALCULATE([Sightings per Year],
Observations[TTL Min]>[Avg Sighting Length in Mins])

In the first formula, I was trying to use a measure on the left side of the comparison, and in the second, I was trying to use a measure on the right side of the comparison.  Both are illegal.

Read on to see why and how you can use FILTER() to solve these problems.

Comments closed

SUMX() in Power BI

Rob Collie explains the power of SUMX() in DAX:

Have you ever written an array formula in Excel?  (Don’t worry, most people haven’t).  Have you ever written a FOR loop in a programming language?  (Again, don’t worry, there’s another question coming).  Have you every repeated something over and over again, slowly building up to a final result?

That’s what SUMX() does.  It loops through a list, performs a calc at each step, and then adds up the results of each step.  That’s a pretty simple explanation, but it produces some results in pivots that are nothing short of spectacular.

Read on for a few examples.

Comments closed

Debugging DAX Variables

Imke Feldmann has a lengthy Power Query script to help debug issues with DAX variables:

When you’re dealing with a beast like DAX you can use any help there is, right? So here I show you how you can debug DAX variables who contain tables or show the result of multiple variables at once. So you can easily compare them with each other to spot the reason for problems fast.

Please note, that currently only comma separated DAX code is supported.

Click through for a demo as well as a video.

Comments closed

Power BI: Showing Only Slicers with Data

Kasper de Jonge shows us a few new tricks with Power BI:

As of this month Power BI finally supports filtering slicers down to only show rows that have fact data. Before the only thing you could do to achieve this was some workaround like: I described here where you filter down the dimension using a calc table. The other approach was to use Bi Directional cross filtering which would filter down the dimension table appropriately. This leads to performance issues though.

Now you can use a measure to filter down the slicer. 

Click through for an example as well as a few other tricks you can do as a result.

Comments closed

CONCATENATEX in DAX

Alberto Ferrari shows us how the CONCATENATEX function works in DAX:

At this point, extracting the first row from Results which would contain the string to produce in the report, is sufficient. TOPN is the function extracting that row, but there is a major drawback here: in case of a tie, TOPN returns all values involved.

In case TOPN returns multiple rows, it is necessary to show them one after the other so to make it clear that the result is not unique. CONCATENATEX is an iterator that concatenates strings and produces a single string out of a table. 

This is a good demonstration of a useful function.

Comments closed

Importing Performance Analyzer Results into DAX Studio

Marco Russo shows how you can take data from Performance Analyzer and load it into DAX Studio:

DAX Studio helps you navigate through the performance metrics; it improves the productivity in executing one or more of the collected queries, reducing the number of copy/paste operations required. In order to use the DAX Studio feature, first you must export the data collected by Performance Analyzer in Power BI Desktop.

Click through to see how it’s done.

Comments closed

Dynamic Top N in Power BI

Gerhard Brueckl shows how to create a Top N slicer in Power BI, as well as some of the problems you might need to work through:

As I said, this pretty much depends on the business requirements and after discussing that in length with the users, the solution is usually to simply add an “Others” row that sums up all values which are not part of the TopN items. For regular business users this requirement sounds really trivial because in Excel the could just add a new row and subtract the values of the TopN items from the Grand Total.

However, they usually will not understand the complexity behind this requirement for Power BI. In Power BI we cannot simply add a new “Others” row on the fly. It has to be part of the data model and as the TopN calculations is already dynamic, also the calculation for “Others” has to be dynamic. As you probably expected, also this has been covered already:
Oraylis – Show TopN and rest in Power BI
Power BI community – Dynamic Top N and Others category

This is a pain point that ideally I’d like the Power BI team to address. Gerhard does a good job showing how to do it, but Tableau has that (and more) built in.

Comments closed

Debugging DAX Calculations

Imke Feldmann has a debugger measure for DAX:

This is a measure that returns a text-value, showing the number of rows of the adjusted filter context table, the MIN and MAX value of the selected column as well as up to the first 10 values. Just place this measure beneath the CALCULATE-measure in question and try to find the error 

Just have in mind, that this only works for standalone CALCULATE-functions and not for those who are nested in other functions (who modify the evaluation context).

This looks to be quite useful.

Comments closed

RANKX Across Multiple Columns

Jason Baldessari continues a series on RANKX in DAX:

What happens when we need to rank using multiple criteria?  In the example below, we are going to look at resellers by name and key in a SalesTerritoryGroup, and we are going to rank them based on the number of items they have sold.

Let’s start with the model.  I did slightly modify Rob’s original 3 table model approach.  I now have 5 tables here, but the model conceptually still works the same.  I have a sales table, a date table,  a resellers table,  and two lookup tables, one for geography and one for sales territory.

Read the whole thing.

Comments closed

Getting Prior Year’s Year-To-Date with DAX

Kasper de Jonge takes a look at how to calculate a prior year’s year-to-date over the same period as the current year:

Well maybe.. what happens here is that the DAX engine took the whole date range we have in context and shifts it back 12 months. This means for year 2019 it will use January first to December 31. So we get the entire year, is that what we want? Or do we want to see the sales for the previous year until the day we have data for this year so we can compare? Both need different DAX so let’s take a look.

Read on for a detailed analysis, including where you might go wrong.

Comments closed