Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

DistinctCount with filter.

I have the following data:

 

Table of Sellers

Saller_ID,Client_ID,Region
1,1,North
1,2,North
1,3,South
2,4,North
3,5,West
4,6,East
5,7,East
5,8,East
6,9,South
7,10,South
8,11,South

 

Table of Clients

Client_ID
1
2
3
4
5
6
7
8
9
10
11

 

 

I want to produce the following chart:

Region   Distinct Count of Client_ID

North             2

South            4

West             1

East              2

 

But I'm getting this as result:

Region   Distinct Count of Client_ID

North             3

South            4

West             1

East              3

 

 

 How can I distinct count ID by Region using a measure?  

 

 

 

  • Sean's avatar
    Sean
    9 years ago

    Well you either want Distinct Sellers or you have the data reversed

    (meaning data in Seller_ID column should be in Client_ID and vice versa)

    Distinct Sellers = DISTINCTCOUNT ( 'Sellers Table'[Seller_ID] )

5 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable

      Hi Vvelarde, I forgot to mention that I need to do it using a measure. Can you help me?

      • Vvelarde's avatar
        Vvelarde
        Community Champion

        Anonymous

         

        Hi, try this:

         

        TheMeasure = DISTINCTCOUNT(Table1[Id])