New Functions – GENERATESERIES, SELECTEDVALUE

Level: Beginners/Intermediate

In the August 2017 update of Power BI Desktop Microsoft released the new What If feature (first demonstrated at MDIS in July).  This new What If capability uses two new DAX functions to complete a task that you have always been able to do manually.  You still can of course still do it manually, but now it is easier than ever as the new functions make the task of creating a disconnected table/slicer very easy indeed.  I am not actually sure if these 2 new functions were created in order to make the What If process easier or if that was a co-incidence.  Regardless, let me explain the new underlying DAX functions and how they work so you can use these without the What If button if you choose.

Both of the new functions are currently only available in Power BI (to my knowledge).  I know for sure they are not currently available in Excel.

The Old Way

I have been doing what the new What If tool does manually for years (ie allow a slicer to pass possible values to a measure as input).  The old way (still the current way if you are using Power Pivot for Excel) is as follows:

• Create a list of values you want to pass as a what if input to a DAX measure.
• Load that list of values to a table in the data model.  Don’t connect the table to any other table
• Add a slicer to your report from the new table.
• Create a “harvester measure” to “harvest” which value has been selected in the slicer by the user.
• Use the value returned by the harvester measure inside another measure so you can test out various scenarios.

The new What If tool provides an automated (and easier) way to complete this process. Today I am going to cover the 2 new underlying DAX functions that are automatically used by the What If button to streamline this process.

GENERATESERIES()

This new function is used to generate the table of possible values – it has the following syntax:

GENERATESERIES(StartValue, EndValue, [IncrementValue])

The function returns a table, therefore if you are going to use this function manually you either need to use the New Table button (which can return a table), or alternatively (potentially) you could use it as a table input into another DAX function.  I have an idea in my head where you could write a function that compounds interest over a number of years by cycling through such a virtual table, without ever materialising the table itself. Anyway, I digress – let’s look at the new function.

First let me create a sample table to show how it works.  To do this I clicked on New Table in Power BI Desktop and then added the following DAX formula.

myTable = GENERATESERIES(1,10,0.1)

This created the following table (containing 91 rows)

The column name VALUE is automatically applied to the column, but you can rename it if you don’t like this default name.

UNION Solves a Problem

The biggest “gap” I see with this approach vs “the old way” is that each value is equally spaced by the same ‘increment’ amount.  But what if you want to have a table from 1..10 with a step of 1, then 20..100 with a step of 10?  Well actually this is pretty easy.  To do this, first create DAX for a second table as follows:

GENERATESERIES(20,100,10)

Then combine the DAX for both tables into a single New Table using a UNION function as follows:

myNewTable = UNION(GENERATESERIES(1,10,1),GENERATESERIES(20,100,10))

This all works fine and as expected.

You can join/append as many tables as you like using UNION to create non sequential number range.

UNION vs UNION ALL

It is worth pointing out here that UNION in DAX is the same as UNION ALL in SQL.  Consider the following DAX formula

myNewTable = UNION(GENERATESERIES(1,10,1),GENERATESERIES(10,100,10))

When I combine these using UNION in DAX, I get the table below – see in the image below that the table has the value 10 twice.

This is easy to fix by wrapping the entire UNION table function inside a DISTINCT function as below.

myNewTable = DISTINCT(UNION(GENERATESERIES(1,10,1),GENERATESERIES(10,100,10)))

If you don’t remove the duplicates, you may be undesired behaviour.

SELECTEDVALUE()

This new function lets you “check” which value is selected by a user in a table (like the one above) – it has other uses too.  Rob Collie coined the term “Harvester Measure” for these little suckers.  But before I cover this new function, first let me talk about the VALUES() function.  VALUES() returns a single column table of all the values in the current filter context.  But it also has a very special property.  If there is 1 and only 1 value selected in the current filter context, then you can display that value in a card or any other visual.  But you must first protect the visual against the case where there is more than 1 value in the VALUES() table.  To protect against this, you must use the following construct

=IF(HASONEVALUE(table[Column]),VALUES(Table[Column]))

The IF/HASONVALUE is simply protection against this undesired scenario (in this case) where the formula returns more than one value (hence it automatically turns back into a table – that is bad).

The new function SELECTEDVALUE() is simply a replacement for this cumbersome construct above (syntax sugar I assume).  The syntax of the new function is as follows

SELECTEDVALUE(table[Column],[AlternateResult]).  If the optional alternate result is omitted, the function will return BLANK() in the instance where there is something other than 1 selected value in the current filter context.  See the example below.

SelectedValue = SELECTEDVALUE(myNewTable[Value]).  Note below there is nothing selected in the slicer and hence the card returns (Blank).

In the instance where there is a single value selected, that value is returned to the visual (a card in this case as shown below).

The What If Button

The new What If Button simply produces a new table using the GENERATESERIES() function and also produces a new measure to display the single selected value using the SELECTEDVALUE() function.

This is yet another step towards making Power BI accessible to people that are not familiar with DAX.  A word of caution however (as always).  The more of these “make it easy” features that are delivered, the more you will get into the detail before realising you don’t really understand the concepts of filter context, row context and context transition.  To learn this properly, there is no substitute for some formal learning in either a live class, an online class or a good book.

DAX Quick Reference Guide

These 2 new functions are included in my DAX Quick Reference Guide that you can download for free from my shop.