Page 1 of 1

COUNTROWS + VALUES vs DISTINCTCOUNT?

Posted: Mon Apr 16, 2018 1:11 am
by edhans
I'm working my way through the Supercharge PowerBI book and in chapter 12.

What is the advantage of using a measure like:
= COUNTROWS ( VALUES (Table[Column]))
vs
= DISTINCTCOUNT (Table[Column])

For that matter, why use VALUES at all? It seems to me DISTINCT does the same thing, but the naming of it is more intuitive. What is the difference? DISTINCT and DISTINCTCOUNT seem to be a better solution, but I see VALUES used in a lot of examples, and not sure why.

Re: COUNTROWS + VALUES vs DISTINCTCOUNT?

Posted: Mon Apr 16, 2018 6:51 am
by MattAllington
edhans wrote:

= COUNTROWS ( VALUES (Table[Column]))
vs
= DISTINCTCOUNT (Table[Column])


There is no one right or wrong way. Sometimes one way is better but even then it is often personal preference. My book is designed to teach you reusable concepts, and in this case I was teaching you that you can create virtual tables. VALUES(Table[Column]) is such a virtual table, and to prove it i showed you that you could "countrows".

For that matter, why use VALUES at all? It seems to me DISTINCT does the same thing, but the naming of it is more intuitive.

VALUES will return any blanks as rows, DISTINCT does not. A blank will be created on the 1 side of a relationship (ie in a LOOKUP table) when there is a record in the data table with no matching record in the lookup table.

DISTINCT and DISTINCTCOUNT seem to be a better solution, but I see VALUES used in a lot of examples, and not sure why.

I think it is just personal preference, and probably what you learn. I learnt VALUES and that's what I teach.

Re: COUNTROWS + VALUES vs DISTINCTCOUNT?

Posted: Fri Apr 20, 2018 6:54 pm
by Oxenskiold
Hi,
VALUES() is used over DISTINCT() because it's the safe bet. It'll not hide aggregations due to the blank row phenomena as mentioned by Matt.

Most DAX functions that don't take a table expression as a parameter and at the same time return a table use VALUES() internally for the same reason: SUMMARIZECOLUMNS(), ALL(), FILTERS(), ALLSELECTED() etc.

I know of only 4 DAX functions that use DISTINCT() internally: DISTINCT(), DISTINCTCOUNT(), ALLNOBLANKROW() and RELATEDTABLE().

Best regards Jes.

Re: COUNTROWS + VALUES vs DISTINCTCOUNT?

Posted: Sat Apr 21, 2018 7:36 am
by MattAllington
How do you know this stuff Jes? :D

Re: COUNTROWS + VALUES vs DISTINCTCOUNT?

Posted: Sun Apr 22, 2018 12:47 am
by Oxenskiold
Hi Matt, well mainly from trying to get to grips with DAX and not the least being inquisitive.

The blank row issue, the difference between VALUES() and DISTINCT() is some of the very very basic of DAX that is not at all well understood. ( It took me a while to grasp how important it actually is )

I have attached an image to pique your curiosity. It probably is a bit difficult to understand at first but the main message behind is that it only takes 4 table expressions to understand the foundation of DAX. ( I hope I'll not regret that sentence 8-) )

The 4 core DAX expressions.png
The 4 core DAX expressions.png (70.16 KiB) Viewed 399 times


It may help to remember Jeffrey Wang's blog post http://mdxdax.blogspot.dk/2011/03/logic ... table.html and in particular this excerpt.

….
In DAX, the filters in a filter context apply to following DAX table expressions:
1. A table expression that is simply a table reference, such as FactSales.
2. Values(Table[Column]).
3. Distinct(Table[Column]).
In cases of 2 and 3, the Table is filtered by filter context and then distinct values of [Column] are extracted from the filtered table.
….