DAX Table Functions (Part 2)
In this blog post on the DAX ‘Data Bear’ necessities, we will build on our DAX table functions from part 1.
This is the second part of our table functions series. In the first part of our two-part table functions series, we looked at the ALL and FILTER functions and how to use these functions together. In this blog post, we are going to look at some new table functions, VALUES, DISTINCT, ALL and ALLNOBLANKROW.
Again, note here that all the above-named functions are all table functions, meaning that their output is a table. Let’s have a look at how these functions work.
If you prefer to watch the YouTube video, rather than reading the blog, please see the video below:
DAX VALUES
The VALUES function returns distinct values in a column and it only returns the values based on the current filter context. For example, if we filter by item name and we want to return the values for the total sales, the list will only include the values based on our filter selection, thus excluding some values based on our filter conditions. Unlike ALL it takes the current filter context into account and it includes the additional ‘no match row’, which will be explained in this blog.
Referencing back to our previous post, we can see the usefulness of the ALL function, if you want to return all the distinct values for item names, we would use the ALL function to clear any filters that might be present. See the below syntax for the VALUES table function, it expects a table or column from which the values are to be returned:
VALUES(<TableNameOrColumnName>)
Let’s get a grip on the ‘no match row’ concept and how it can affect your calculations.
NO MATCH ROW CONCEPT
Let’s create a scenario in Excel. I’ve put together a simple model with two tables, an ‘Items’ table with an ‘ItemID’ and ‘ItemName’.
And we’ve got a Sales table with an ‘ItemID’ and the total number of ‘Sales’ for each of those ‘ItemIDs’.
The one thing I would like you to note is that in our ‘Items’ table we have an ‘ItemID’ of 1 to 4 and in our ‘Sales’ table we have an additional item, namely ‘ItemID’ 5 which is not available in the ‘Items’ table. So now the question is, is this important and how does this affect our model when we do calculations? Let’s open the diagram view in Excel to explain this better so that we can see what the relationship is between the ‘ItemID’ of the ‘Items’ table as well as the ‘ItemID’ of the ‘Sales’ table.
From the relationship, we can see that there is a one to many relationships between the ‘Items’ table and the ‘Sales’ table.
Going back to our Excel file, let’s create a pivot table to explain the ‘no match row’ concept. Let’s add the ‘ItemName’ from the ‘Items’ table, we immediately see Excel and Power BI for that matter creates a blank row, why is this? The table created a row for each name from the ‘ItemName’ column, but note the addition here that may be a bit unexpected if you are not familiar with this concept. The pivot table created an extra blank row and this blank is created from the relationship that exists between the ‘Sales’ table and the ‘Items’ table. The extra ‘ItemID’ 5 is the extra ‘ItemID’ in the ‘Items’ table that does not belong to any ‘ItemID’s’ in the ‘Items’ table and therefore Excel/Power BI returns a blank row.
DAX VALUES FUNCTION
Remember that we said VALUES returns the distinct values in a column and it returns the values based on the current filter context. Let’s put the VALUES table function to the test.
Example Question:
The following example question will be used throughout this blog post to evaluate the functions. Let’s say we would like to count the number of distinct rows in the ‘ItemName’ column of the ‘Items’ table.
We will call our measure ‘VALUES’. We use the COUNTROWS function to count every row for VALUES of the ‘ItemName’ column from the ‘Items’ table.
VALUES = COUNTROWS(VALUES(Items[ItemName]))
Let’s insert VALUES into our values field of the Pivot Table. We can see that VALUES counts the rows for each item and it even counts the one where the row is blank. It gives us a count of 5.
DAX DISTINCT
Let’s test what the DISTINCT table function does. DISTINCT returns the distinct values in a column, taking into account the current filter context. It is similar to ALL, the difference being ALL always returns all values, ignoring filters.
Example Question: DISTINCT
We will call our measure ‘DISTINCT’. We use the COUNTROWS function to count every row for DISTINCT of the ‘ItemName’ column from the ‘Items’ table.
DISTINCT = COUNTROWS(DISTINCT((Items[ItemName])))
If we drag this over onto our canvas, you’ll notice that it does not count blank values, DISTINCT only count non-blank rows.
DAX ALL and ALLNOBLANKROW
Let’s also look at the ALL and ALLNOBLANKROW table functions. ALL as you know returns all the rows in the table ignoring all existing filters. ALL requires either a table or a table column as an input.
Example Question: ALL
We will call our measure ‘ALL’. We use the COUNTROWS function to count every row for ALL of the ‘ItemName’ column from the ‘Items’ table. The measure will look as follow:
ALL = COUNTROWS(ALL(Items[ItemName]))
Example Question: ALLNOBLANKROW
We will call our measure ‘ALLNOBLANKROW’. We use the COUNTROWS function to count every row for ALLNOBLANKROW of the ‘ItemName’ column from the ‘Items’ table. The measure will look as follow:
ALLNOBLANKROW = COUNTROWS(ALLNOBLANKROW(Items[ItemName]))
If we drag the ALL and ALLBLANKROW into our pivot table, we can see something very interesting happening here. The table function ALL has returned a count of 5 for the number of rows, we see that ALL includes the blank row.
The ALLNOBLANKNOROWS table function has returned all items excluding the blank row.
So immediately you can see that there is a key difference between VALUES and DISTINCT and ALL and ALLNOBLANKROW.
Let’s see how this becomes useful with an example. Let’s say we want to calculate the percentage of sales per item. If we use the ALL table function and there exists a blank item in our table, the calculation will be slightly skewed depending on how many rows or items are blank. This will skew the percentage of sales you have across all your cells.
This is a simple example of why it is so important to know how your different DAX functions operate to ensure that you arrive at the correct calculations.
That’s it for this blog post and I hope you enjoyed it, see you next time. For more go to our blog page.
Leave A Comment