Distinctcount based on two columns

[mstefancik]

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 shown in the picture, correct result I am expecting is 4.

Any suggestions? Thx

[v-qiuyu-msft]

Hi @mstefancik,

In addition, you can also use "Append Queries" feature in Query Editor to append Table2 to Table1. The new table looks like below:

Then create a measure to return the count of distinct values:

If you have any question, please feel free to ask.

Best Regards,
Qiuyun Yu

[sdjensen · Accepted solution]

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]

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