DistinctCount not showing the correct numbers

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.

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:

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.

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

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')
)

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

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.

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