Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

count doing an Inner join

Hello,   Basically what I want to get the below output   select count(distinct c.email) from dbo.transactions t inner join dbo.Contributors c ON (t.ContributorId=c.Id)   Table Transactions is...
Need Help
Show and Tell
Cmcmahan's avatar
Cmcmahan
Resident Rockstar
7 years ago

This looks like you can just set up a relationship between your tables and then use this measure to get the count of distinct email addresses:

 

CountDistinctEmail = DISTINCTCOUNT('Contributors'[Email])

If you're trying to get a column of all the different values in the 'Contributors'[Email] column for future use you can use:

DistinctEmailValues = VALUES('Contributors'[Email])
  • HotChilli's avatar
    HotChilli
    Community Champion
    7 years ago

    Adding a relationship and using DISTINCTCOUNT would be the same as just using DISTINCTCOUNT so I don't think that's what the question is looking for.

     

    For the inner join part to be taken into consideration i can think of a couple of ways (both require the relationship on contributorid) :

    a calculated table

    Table = SUMMARIZE (
txn,
"Dist Emails", DISTINCTCOUNT( Contributors[email] )
)

    OR

    a measure

    distinct mails with txn = CALCULATE(DISTINCTCOUNT(Contributors[email]), FILTER(txn,txn[tid] > 0))