AX offers two functions to retrieve the list of values of a column: VALUES and DISTINCT. The distinction between the two is scrupulous. To know it higher, we tend to initially ought to introduce the conception of the blank row. The blank row could be a special row added to a table, just in case the table is on the one-side of a powerful relationship, and also, the relationship is invalid. Let allow to elaborate on this.
Consider two measures to compute the number of colors, using the two functions VALUES and DISTINCT:
#Colors Distinct = COUNTROWS ( DISTINCT ( 'Product'[Color] ) ) #Colors Distinct = COUNTROWS ( VALUES ( 'Product'[Color] ) )
The representation of these two measures in a matrix reveals the same values, both on the matrix’s individual rows and at the grand total.
The two functions appear identical. The reason is that all the relationships within the data model are in good shape. To notice a difference between DISTINCT and VALUES, we want to invalidate the connection between Sales and products. We will do that by removing a few products from the Product table. For example, we tend to remove all the products with a pink color. This can be the result once deleting the pink products:
As you see, two lines within the report are currently completely different. A new line with a blank brand has appeared at the top, and also the grand total shows completely different values: VALUES returns 16 colors, whereas DISTINCT returns 15 colors.
This is happened by removing products; there are currently several rows in Sales with a value for Sales[ProductKey] that doesn’t match any row in Product. In other words, the relationship is now invalid. If a relationship is invalid, the Power BI engine automatically adds a row containing BLANK in each column of the table on the one side of the relationship – during this case to the Product table. Then, it links all the rows on the many-side to the blank row on the one-side. Therefore, all the various values of Sales[ProductKey] with no corresponding row in the Product are connected to a single blank row.
In the figure below, you’ll see a visual representation of the blank row.
The blank row is special. Some functions consider it as a regular row, whereas other functions don’t. For example, the VALUES function consider the blank row as a regular row, returning it. Whereas, DISTINCT never returns the blank row.
This is the reason why VALUES and DISTINCT produces different results. VALUES reports one extra row (the blank row), whereas DISTINCT doesn’t. If the relationship is valid, that’s if all the rows in Sales contain a value that matches one row in Product, then the two functions behave identically.
An experienced DAX developer & Power BI consultants are aware of the functions that consider the blank row and the ones that ignore the same row. For example, imagine you simply wish to count the products. A simple DAX is the following:
#Products = COUNTROWS ( 'Product' )
You can see that for the blank row, a Product is missing. This is because – when you reference a table – the blank row is never returned as if it weren’t a part of the table. If you wish to consider the blank row as a valid row, then you need to perform the counting differently:
#Products Values = COUNTROWS ( VALUES ('Product' ) )
The figure below shows the difference between these two measures.
There are multiple scenarios where the difference is important. For example, one might want to compute the number of each brand’s products divided by the number of all brands ‘ products. This can be done with a simple DAX calculation like the one below:
Perc #Prods = DIVIDE ( COUNTROWS ( 'Product' ), COUNTROWS ( ALL ( 'Product' ) ) )
Despite looking very simple, this code is glitched. This generates a number when used in a report that does not add up to 100 percent.
The reason for this is that at the numerator, we have just used the table reference Product that doesn’t take under consideration the blank row. At the denominator, on the other hand, we have used ALL ( Product ). ALL consider the blank row as valid. Therefore, the numerator is 2,433, and consequently, the denominator reports 2,434, resulting in an incorrect calculation. The problem will be corrected by changing either the numerator using VALUES or the denominator using ALLNOBLANKROW. Contrary to ALL, ALLNOBLANKROW doesn’t come back the blank row:
Perc #Prods Values = DIVIDE ( COUNTROWS ( VALUES ( 'Product' ) ), COUNTROWS ( ALL ( 'Product' ) ) ) Perc #Prods Distinct = DIVIDE ( COUNTROWS ( DISTINCT ( 'Product' ) ), COUNTROWS ( ALLNOBLANKROW ( 'Product' ) ) )
You can see the results in the figure below
Beware that all these calculations give slightly different results. As a rule, one should never work with invalid relationships. Validating relationships is a very important part of the data quality assurance process. Therefore just in case you face a model that may contain invalid relationships, then it’s mandatory that you simply check all of your measures.
Query and suggestions are always welcome. If you have a similar need, do .