DistinctCount not showing the correct numbers

[gowtham1991]

Dear Together,
I am facing an issues while using disctinct count in the Dax column.

Issue description:

I have two tabled A and B as below , with relationship many to many.

Table A
ID	Data
12	A
13	B
14	C

Table B
CustID	ID
AA	12
AB	13
AC	12
AD	12
AA	12

I am creatting a DAX custom column using query Calculate(distinctCount(TableA[ID])) to bring the expected result as below

Expected Results
ID	Data	Count of CUST ID (DAX Column)
12	A	3
13	B	1
14	C

But I am not getting the correct numbers as expected and result comes as below

Actual  Results
ID	Data	Count of CUST ID (DAX Column)
12	A	1
13	B	1
14	C

Not sure what is wrong with relation or query, 

Thanks

Gowtham

[Greg_Deckler · Accepted solution]

@gowtham1991 That looks correct to me, you have 1 12 (A), 1 13 (B) in Table A. You have no 14 in Table B. Is your measure supposed to count TableB or TableA because you are saying it is counting TableA which seems wrong. Plus, would you want to be counting TableB[CustID] because those are the distinct values AA, AC, AD for 12 for example but you are saying you are counting TableA[ID] which doesn't make sense.

[Bibiano_Geraldo · Accepted solution]

hI @gowtham1991 ,

In your table A, create a new calculated column by this DAX:

Count of CUST ID = 
CALCULATE(
    DISTINCTCOUNT('Table B'[CustID]),
    FILTER(
        'Table B',
        'Table B'[ID] = 'Table A'[ID]
    )
)

Your output should look like this:

[gowtham1991 · Accepted solution]

hello @Greg_Deckler 

thanks for your replay, The query used is  Calculate(distinctCount(TableB[ID])). Still with this query I am facing the same issue.

[gowtham1991 · Accepted solution]

@Bibiano_Geraldo  Thanks you for your replay, Unfortunately this does not solve the issue. Note:, Not all the values are wrong, I have only few rows showing a incorrect date. 

In the acatual fileI have atmost 300K rows and nealy 100+ rows showing incrrect values a

[Bibiano_Geraldo · Accepted solution]

Hi @gowtham1991 ,

A many-to-many relationship can cause issues with DISTINCTCOUNT calculations. A better approach is to create a bridge table to normalize the relationship between Table A and Table B.

You need a unique list of ID values to act as a bridge:

BridgeTable = DISTINCT(UNION(SELECTCOLUMNS('Table A', "ID", 'Table A'[ID]), SELECTCOLUMNS('Table B', "ID", 'Table B'[ID])))

Set Up Relationships:
Relate BridgeTable[ID] one-to-many with Table A[ID].
Relate BridgeTable[ID] one-to-many with Table B[ID]. 

Now, use this measure to get the distinct count of CustID dynamically:

Count of CUST ID = 
CALCULATE(
    DISTINCTCOUNT('Table B'[CustID]),
    RELATEDTABLE('Table B')
)

[Anonymous · Accepted solution]

Hi, @gowtham1991 

Thanks for the reply from Bibiano_Geraldo and Greg_Deckler. You can refer to their suggestions, or you can refer to the following ways to achieve your need.

Relationship: Many to many, single A to B.

Method 1:
Add the filed, and change the filed of CustID to Count of CustID.

Method 2:
Use this measure.

Measure Count of CustID = CALCULATE(DISTINCTCOUNT('Table B'[CustID]))

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[gowtham1991 · Accepted solution]

hello @Anonymous 

Thanks for your suggestions, unfortunatly I have to user this as a column, as I have a another custom column getting created using the values form this column.

[gowtham1991]

@Anonymous @Bibiano_Geraldo @Greg_Deckler 

Dear Experts, With all the suggestions, Created the measure and called the measure as a column required table and this solved the issue

Thanks

Gowtham

[Anonymous · Accepted solution]

Hi, @gowtham1991 

Thanks for the reply from Bibiano_Geraldo and Greg_Deckler. You can refer to their suggestions, or you can refer to the following ways to achieve your need.

Relationship: Many to many, single A to B.

Method 1:
Add the filed, and change the filed of CustID to Count of CustID.

Method 2:
Use this measure.

Measure Count of CustID = CALCULATE(DISTINCTCOUNT('Table B'[CustID]))

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[gowtham1991 · Accepted solution]

hello @Anonymous 

Thanks for your suggestions, unfortunatly I have to user this as a column, as I have a another custom column getting created using the values form this column.

[Anonymous · Accepted solution]

Hi, @gowtham1991 

Bibiano_Geraldo also gave a good advice, you can check it. The formula works fine with less amount of data, not sure what is the issue on your end, you can share the pbix file you expect to realize the output and share it without sensitive data for testing.

Best Regards,
Yang
Community Support Team

[Bibiano_Geraldo · Accepted solution]

hI @gowtham1991 ,

In your table A, create a new calculated column by this DAX:

Count of CUST ID = 
CALCULATE(
    DISTINCTCOUNT('Table B'[CustID]),
    FILTER(
        'Table B',
        'Table B'[ID] = 'Table A'[ID]
    )
)

Your output should look like this:

[gowtham1991 · Accepted solution]

@Bibiano_Geraldo  Thanks you for your replay, Unfortunately this does not solve the issue. Note:, Not all the values are wrong, I have only few rows showing a incorrect date. 

In the acatual fileI have atmost 300K rows and nealy 100+ rows showing incrrect values a

[Bibiano_Geraldo · Accepted solution]

Hi @gowtham1991 ,

A many-to-many relationship can cause issues with DISTINCTCOUNT calculations. A better approach is to create a bridge table to normalize the relationship between Table A and Table B.

You need a unique list of ID values to act as a bridge:

BridgeTable = DISTINCT(UNION(SELECTCOLUMNS('Table A', "ID", 'Table A'[ID]), SELECTCOLUMNS('Table B', "ID", 'Table B'[ID])))

Set Up Relationships:
Relate BridgeTable[ID] one-to-many with Table A[ID].
Relate BridgeTable[ID] one-to-many with Table B[ID]. 

Now, use this measure to get the distinct count of CustID dynamically:

Count of CUST ID = 
CALCULATE(
    DISTINCTCOUNT('Table B'[CustID]),
    RELATEDTABLE('Table B')
)

[Greg_Deckler · Accepted solution]

@gowtham1991 That looks correct to me, you have 1 12 (A), 1 13 (B) in Table A. You have no 14 in Table B. Is your measure supposed to count TableB or TableA because you are saying it is counting TableA which seems wrong. Plus, would you want to be counting TableB[CustID] because those are the distinct values AA, AC, AD for 12 for example but you are saying you are counting TableA[ID] which doesn't make sense.

[gowtham1991 · Accepted solution]

hello @Greg_Deckler 

thanks for your replay, The query used is  Calculate(distinctCount(TableB[ID])). Still with this query I am facing the same issue.