Forum Discussion
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 shown in the picture, correct result I am expecting is 4.
Any suggestions? Thx
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])
3 Replies
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])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')
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
