Distinct Count where columns match

[bmcomp]

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]

@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]

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 · Accepted solution]

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

[bmcomp]

Hi @Eric_Zhang

That works perfect.  I really appricate your help on this!

Regards

Brian