Does DISTINCT not ignore BLANK values anymore?

[Jeroen_de_Bondt]

Hi! I was working on a DAX training and I came something that I can't get my head around yet. I was under the assumption that the one of the differences between the VALUES and DISTINCT functions is the handling of BLANK values. See below my calculated column where on the right you can see there are BLANK values in my customer potential column.

Now if I use the following two measures in both cases I get a result of 5, where I would expect a result of 4 in the DISTINCT example as that should (what I thought, but maybe I am wrong or doing something wrong) ignore the BLANK value right?

Distinct example (DISTINCT) =

COUNTROWS(

    DISTINCT(DimCustomer[Customer Potential])

)

and 

Values example (VALUES) =

COUNTROWS(

    VALUES(DimCustomer[Customer Potential])

)

See an image below:

And if I add a new table it also shows the BLANK value in the DISTINCT example. I thought in this case the BLANK should be ignored?:

Curious if someone can explain to me what I am doing wrong or missing ? 

[saurabhtd · Accepted solution]

@Jeroen_de_Bondt

If a relationship is invalid, the VertiPaq engine automatically adds a row containing BLANK in every column of the table on the one-side of the relationship. Then, it links all the orphaned rows on the many-side to the blank row on the one-side.

The VALUES function treats the blank row as a regular row, returning it. On the other hand, DISTINCT never returns the blank row, limiting its result to rows physically loaded in the table. This is the reason why counting VALUES and DISTINCT produces different results. VALUES reports one additional row (the blank row), whereas DISTINCT does not. If the relationship is valid, then the two functions behave identically.

In your case Customer Potential is blank when YearlyIncome is less than 20000, so only customer potential column will be blank other coumns will have data. So this not the blank row. Also relationship is vaild, that's why both Distinct and Values are behaving identically.

You can read this article for better clarity. 
https://www.sqlbi.com/articles/blank-row-in-dax/ 

[123abc]

The behavior you are observing is consistent with how the DISTINCT and VALUES functions operate in DAX, particularly with respect to handling BLANK values.

When using the DISTINCT function in DAX, it does not ignore BLANK values. Instead, it treats BLANK as a distinct value, which is why you see a count of 5 in your Distinct example (DISTINCT) measure.

On the other hand, the VALUES function returns a one-column table that contains the distinct values from the specified column, excluding BLANK values. That's why you see a count of 4 in your Values example (VALUES) measure.

To summarize:

• DISTINCT: Includes BLANK values as a distinct value.
• VALUES: Excludes BLANK values from the returned table.

So, if you want to ignore BLANK values and get the count of distinct non-BLANK values, you should use the VALUES function as you did in your example.

If you want to handle BLANK values differently, you might need to use additional logic or functions to filter or replace BLANK values as per your requirements.

[Jeroen_de_Bondt]

Hi, thanks for your reply. But in both cases the result if 5 instead of what you are writing  a 4 for the values example...