## COUNTROWS + VALUES vs DISTINCTCOUNT?

Anything related to PowerPivot and DAX Formuale
edhans
Posts: 1
Joined: Wed Jan 25, 2017 4:04 am

### COUNTROWS + VALUES vs DISTINCTCOUNT?

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.

MattAllington
Posts: 985
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

### Re: COUNTROWS + VALUES vs DISTINCTCOUNT?

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.
Matt Allington is Self Service BI Consultant, Trainer and Author of the Book "Supercharge Power BI".
Power BI Training

Oxenskiold
Posts: 35
Joined: Tue Jan 05, 2016 10:38 pm

### Re: COUNTROWS + VALUES vs DISTINCTCOUNT?

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.

MattAllington
Posts: 985
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

### Re: COUNTROWS + VALUES vs DISTINCTCOUNT?

How do you know this stuff Jes?
Matt Allington is Self Service BI Consultant, Trainer and Author of the Book "Supercharge Power BI".
Power BI Training

Oxenskiold
Posts: 35
Joined: Tue Jan 05, 2016 10:38 pm

### Re: COUNTROWS + VALUES vs DISTINCTCOUNT?

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 )

The 4 core DAX expressions.png (70.16 KiB) Viewed 793 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.
….