Forum Discussion

mstefancik's avatar
mstefancik
Icon for Advocate IV rankAdvocate 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 shown in the picture, correct result I am expecting is 4.

 

Any suggestions? Thx

  • 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])

     

3 Replies

  • sdjensen's avatar
    sdjensen
    Icon for Solution Sage rankSolution 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
      Icon for Solution Sage rankSolution 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')
  • v-qiuyu-msft's avatar
    v-qiuyu-msft
    Icon for Community Support rankCommunity Support
    10 years ago

    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