Controws(Values), DistinctCount & DistinctcountNoBlank

DAX DAX Calculations
1

From what I understood, the Distinct count doesn’t count Blank Rows, but the Values does,

but here I am with a column that has several rows, with unique numbers and blank values that I cant filter out
Now Countrows(values(“columnname”)), Distinctcount(“columnname”) gives me same count, until I use distinctcountnoblank(“columnname”) which gives me a 1 less count

So I guess I didnt understand it correctly can you explain, or may be point to a resource discussing this topic

Thank you.

2

Hi @jps. Try this and see if it helps:

Greg

1 Like
3

@jps The blank row here isn’t the blank value such as BLANK cell, the blank row that you are trying to include/exclude is automatically added by the DAX engine to the 1 side of the relationship if the 1 side is missing values for the rows on the many side.

DISTINCTCOUNTNOBLANK is going to exclude the rows where the cells value is Blank, whereas VALUES and DISTINCT include and exclude the blank row added internally by the DAX engine and not the blank row/cell because a cell’s value is blank.

image
image1139×670 55.5 KB

3 Likes
4

Hi @jps, did the response provided by the experts help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!

5

Hey Antriksh - I had a major confusion around this. It all makes sense now.

@Greg Thank you that video was quite helpful
Thank you.

1 Like