count distinct versus count when the field is used in a relation

[alain_paris]

I have two tables Table1 and Table2 with a relationship "one to many" on the first column  FIELD1

Table 1

-------

FIELD1

V1  + other columns

V1   + other columns

V1  + other columns

V3   + other columns

V4   + other columns

Table 2

-------

FIELD1

V3 + other columns

V4 + other columns

In our model , if different occurence of Table1 have the same value V1 in the first column there cannot be occurence with V1 in table2.  Therefore  , the OneToMany relationship is accepted by PowerBI.

The problem: if buid a matrix using Table 1 with 

FIELD1

Count distinct (FIELD1)

I get 

V1    3

V3    1

V4    1

The "DISTINCT" does not work !!!!

But if I break the relationship between Table1 and Table2, then I have the result I expect

I get 

V1    1

V3    1

V4    1

Is it a bug ?

Alain

[v-ljerr-msft]

@alain_paris

According to your description, I have tested it on my side and the Count(Distinct) works all fine for me no matter if there is a relationship between Table 1 and Table 2. 

Are you using the latest version of Power BI Desktop(2.39.4526.362 64-bit (September 2016)). Could you upload the pbix file which can reproduce this issue in your case?

Regards

[hohlick]

May be this will help: http://sqlblog.com/blogs/marco_russo/archive/2011/03/08/difference-between-distinct-and-values-in-da...

But why did you use COUNT(DISTINCT(...)) ?? Use DISTINCTCOUNT instead