Data Bear – Power BI Training and Consulting

Call us Today! (020) 8720 6880 | info@databear.com

# DAX TABLE FUNCTIONS PART 2 – VALUES, DISTINCT & ALLNOBLANKROW

## 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.