Distinct count summarisation issues

[wardlea1]

Hi all,

I am trying to do a count distinct of ID within my data set but i am struggling. Unfortuantely I am unable to share my data but i will try and explain below.

I am using a tabular model with relationships built between tables. When looking at ID by division and product i get a result similar to the below. Division and product are located in one table and ID is located in another table. There is a relationship between these tables as otherwise I wouldn't be able to get the data below 'linked'

Division	Product	ID
A	1	A001
B	3	A002
C	6	A003
B	3	A004
B	3	A005
B	4	A006
C	7	A007
B	5	A008
C	6	A009
A	2	A010
A	1	A001
B	3	A002

I then want to do a distinct count of the IDs, bringing back the total as 10 (the last two records in the above are duplicates). This is all fine but when it comes to provide a summary at a higher level the results come out the same for all divisions products. Such as the below

Division	ID
A	10
B	10
C	10

Product	ID
1	10
2	10
3	10
4	10
5	10
6	10
7	10

Division	Product	ID
A	1	10
A	2	10
B	3	10
B	4	10
B	5	10
C	6	10
C	7	10

Whereas what I want is the following

Division	Product	ID
A	1	1
A	2	1
B	3	3
B	4	1
B	5	1
C	6	1
C	7	2

Any suggestions?

thanks

[MahyarTF]

Hi,

As your sample data I create two tables in Power BI and made a relationship between them:

- Then create a measure on Sheet with Id :

CntDivision = DISTINCTCOUNT(Sheet114[Id])

- Then use the particular measure in my visual as below :

Appreciate to Kodus

[Jihwan_Kim · Accepted solution]

Hi,

Please check the below picture and the attached pbix file.

I am not sure if I understood your question correctly, but one of ways is to create a measure like below.