Forum Discussion

mstefancik's avatar
mstefancik
Advocate IV
10 years ago
Solved

Distinctcount based on two columns

Hello,   I have got 2 tables with different columns, but wto columns are same, date and customerID. I would like to do distinctcount to count all customerIDs from these two tables.   As sho...
  • sdjensen's avatar
    sdjensen
    10 years ago

    Hi mstefancik,

     

    You could try this:

     

    First calculate a new table having the values of CustomerID from Table1 and Table2

    Table = UNION (VALUES( 'Table1'[CustomerID] ), VALUES (Table2[CustomerID] ) )

     

    And then create a measure that counts the distinct values in the new table

    Distinct Customers = DISTINCTCOUNT('Table'[CustomerID])

     

sdjensen's avatar
sdjensen
Solution Sage
10 years ago

Hi mstefancik,

 

You could try this:

 

First calculate a new table having the values of CustomerID from Table1 and Table2

Table = UNION (VALUES( 'Table1'[CustomerID] ), VALUES (Table2[CustomerID] ) )

 

And then create a measure that counts the distinct values in the new table

Distinct Customers = DISTINCTCOUNT('Table'[CustomerID])

 

  • sdjensen's avatar
    sdjensen
    Solution Sage
    10 years ago

    An alternative solution could be to select the distinct values into you table and then just create a count of the rows in your new table - I would suspect this approach to be faster, but you really need to test this.

     

    Table:

    Table = DISTINCT( UNION (VALUES( 'Table1'[CustomerID] ); VALUES (Table2[CustomerID] ) ) )

    Measure:

    Distinct Customers = COUNTROWS('Table')