Distinct count across two columns

[Alienvolm]

Hi, 

I'm trying to create a measure that calculates the distinct count of active users across two columns, as in the example below:

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

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. 

Any suggestions on how I can accomplish this? 

Thanks! 

Alienvolm

[Greg_Deckler · Accepted solution]

@Alienvolm Essentially MC Aggregations: Multi-Column Aggregations (MC Aggregations) - Microsoft Power BI Community

In your case:

Measure =
  COUNTROWS(
    DISTINCT(
      UNION(
        SELECTCOLUMNS('Table',"Email",[Sender]),
        SELECTCOLUMNS('Table',"Email",[Receiver])
      )
    )
  )

[Alienvolm]

Thanks a lot! That worked!