Count Occurence of string select from 'Table A' in 'Col A' and in 'Col B' of Table B Separetly

[frdehuysser]

Hi, 

I'm a new powerbi user and I'm trying to set up activity counters for mail flow.
I have a table A in which I have the reference list of my users' email addresses
And a second table B from Office 365 message trace in which I have several columns including "Sender","Recipient","Subject","MessageId"
I want to have 2 counters, the number of emails sent and the number of emails received by a user selected in the Slicer from table A.
I tried several ways based on examples found in the Forum, but I did not achieve the expected result.

I've create à measure for Count Sent_Emails By selected User: 

Sent_Emails =
var _d = SELECTEDVALUE('TableA'[My_users])
var _e = HASONEVALUE(''TableA'[My_users])
var _f = countrows(FILTER('TableB','TableB'[sender]))
Return
if (_e, if (ISBLANK(_f), 0, _f), BLANK() )

Any suggestions?

Table A looks like :

My_Users

sarah.croche@Mydomain.fr

anna.conda@Mydomain.fr

Maude.zarella@Mydomain.fr

jessica.netaufrais@Mydomain.fr

Table B looks like : 

sender	recipient	sujet	MessageID
sarah.croche@mydomain.fr	jessica.netaufrais@mydomain.fr	test 1	123456
sarah.croche@mydomain.fr	anna.conda@mydomain.fr	test 1	123456
jack.celere@DomainA.com	sarah.croche@mydomain.fr	test 2	134689
sarah.croche@mydomain.fr	jack.celere@DomainA.com	test 3	258463
camille.honnete@DomainB.com	anna.conda@mydomain.fr	test 4	584694
sarah.croche@mydomain.fr	jack.celere@DomainA.com	test 5	369852
anna.conda@mydomain.fr	sarah.croche@mydomain.fr	test 6	789456
Maude.zarella@mydomain.fr	jack.celere@DomainA.com	test 7	852741

[v-yanjiang-msft]

Hi @frdehuysser ,

According to your description, here's my solution. Create two measures.

Sent_Emails =
COUNTROWS (
    FILTER ( 'TableB', 'TableB'[sender] = SELECTEDVALUE ( TableA[My_Users] ) )
) + 0

Rec_Emails =
COUNTROWS (
    FILTER ( 'TableB', 'TableB'[recipient] = SELECTEDVALUE ( TableA[My_Users] ) )
) + 0

Get the correct result:

I attach my sample below for your reference.

Best regards,

Community Support Team_yanjiang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[frdehuysser]

Great, your formula does the job for a unique selection. On the other hand, if I select several entries from the My users table, the formula Return 0, Do you now if it's possible to display the SUM of multi selected ?
Thanks

[v-yanjiang-msft · Accepted solution]

Hi @frdehuysser ,

Simply modify the formula like this:

Sent_Emails =
COUNTROWS (
    FILTER ( 'TableB', 'TableB'[sender] IN DISTINCT ( TableA[My_Users] ) )
) + 0

Rec_Emails =
COUNTROWS (
    FILTER ( 'TableB', 'TableB'[recipient] IN DISTINCT ( TableA[My_Users] ) )
) + 0

Get the correct result:

I attach my sample below for your reference.

Best regards,

Community Support Team_yanjiang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.