Press "Enter" to skip to content

Category: DAX

Power BI: Showing Only Slicers with Data

Published 2019-07-02 by Kevin Feasel

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

Published 2019-06-26 by Kevin Feasel

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

Published 2019-06-05 by Kevin Feasel

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

Published 2019-05-27 by Kevin Feasel

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

Published 2019-05-24 by Kevin Feasel

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

Published 2019-05-15 by Kevin Feasel

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

Published 2019-05-13 by Kevin Feasel

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

DAX Error: Multiple Columns and Scalar Values

Published 2019-05-01 by Kevin Feasel

Eugene Meidinger walks us through an error message in DAX:

Sometimes, when working with DAX, you might get the following error:

The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.

This error occurs whenever the DAX engine was expecting a single value, or scalar, and instead received  a table of values instead. This is an easy error to make because many DAX functions, such as FILTER, SUMMARIZE and ALL, return table values. 

Eugene lays out when each scenario occurs, so check it out.

Comments closed

Aggregating Only Visible Values in DAX

Published 2019-04-24 by Kevin Feasel

Alberto Ferrari shows how you can calculate a sum based only on the visible values and ignoring the missing cases:

These measures produce the correct figures month by month. However, at the year level the number of working days might be too big whenever there are incomplete months – this always happens before the end of the year, or when the first month with sales is not January as in the following example. As you can see in the following figure, in this case the yearly value of SalesPerWorkingDays is lower than the value of any month, which does not make much sense.

Click through for the solution.

Comments closed