Distinct count across two columns

Question

Hi,&nbsp;&nbsp;I'm trying to create a measure that calculates the distinct count of active users across two columns, as in the example below:&nbsp;&nbsp;In both columns there are 4 active users, but my result would not be the sum of the two distinct counts (8): the result would be 6 (as there are only 6 email addresses in total - sometimes they're senders and sometimes they're receivers).&nbsp;&nbsp;My process would be to append the two columns and then calculate a distinct count of the resulting column, but I have to use a measure as I'm connected in DirectQuery mode.&nbsp;&nbsp;Any suggestions on how I can accomplish this?&nbsp;&nbsp;Thanks!&nbsp;&nbsp;Alienvolm

greg_deckler · Accepted Answer

Anonymous&nbsp;Essentially MC Aggregations:&nbsp;Multi-Column Aggregations (MC Aggregations) - Microsoft Power BI Community
&nbsp;
In your case:
Measure =
  COUNTROWS(
    DISTINCT(
      UNION(
        SELECTCOLUMNS('Table',"Email",[Sender]),
        SELECTCOLUMNS('Table',"Email",[Receiver])
      )
    )
  )

Forum Discussion

Distinct count across two columns

2 Replies

Related content

Distinctive Count across two columns

count distinct

distinct count of multiple columns

Count distinct values in one column, grouped by another column

Getting distinct values for multiple columns