Forum Discussion

bmcomp's avatar
bmcomp
Regular Visitor
10 years ago
Solved

Distinct Count where columns match

Hi All,

 

I have two queries joined by a key. The two queries are Customer details & Responses.  I want to get a count of how many unique customers that have responses.  A customer can have more than one response but i just want to count that customer once.

 

I want to do something like this:

DISTINCTCOUNT (CustomerName) WHERE 'CustomerDetails'[Key] = 'Responses'[Key]

 

Any help would be great

 

Thanks

B

 

  • Eric_Zhang's avatar
    Eric_Zhang
    10 years ago

    bmcomp

    Can you have a try

    No of Customers have at least one response =
COUNTROWS (
    FILTER (
        Customers,
        IF (
            ISBLANK (
                LOOKUPVALUE (
                    Response[CustomerID],
                    Customers[CustomerID], Customers[CustomerID]
                )
            ),
            0,
            1
        )
    )
)

4 Replies

  • Eric_Zhang's avatar
    Eric_Zhang
    Microsoft Employee
    10 years ago

    bmcomp

     

    Say the two tables are as below and a proper relationship is created. Check a measure like

     

    No of Customers have at least one response = COUNTROWS(FILTER(Customers,LOOKUPVALUE(Response[CustomerID],Customers[CustomerID],Customers[CustomerID])))

     

     

    • bmcomp's avatar
      bmcomp
      Regular Visitor
      10 years ago

      Hi Eric_Zhang

       

      Thanks for the reply.  My key is an alphamumeric key and as such the LOOKUPVALUE command will not work.  It will work perfectly if my key was an integer.  I may be able to do some transformation on the column to solve this if there is no other way.

       

      Do you know of any workaround for alphanumeric values?

       

      Thanks

      Brian

      • Eric_Zhang's avatar
        Eric_Zhang
        Microsoft Employee
        10 years ago

        bmcomp

        Can you have a try

        No of Customers have at least one response =
COUNTROWS (
    FILTER (
        Customers,
        IF (
            ISBLANK (
                LOOKUPVALUE (
                    Response[CustomerID],
                    Customers[CustomerID], Customers[CustomerID]
                )
            ),
            0,
            1
        )
    )
)