How to create measure for the emails count in a day

[Anonymous]

I have a task to create a dashboard on the emails count sent for different users in a day. If the recipient count is >=5 by a sender in a day then the details needs to be displayed. Need help on how to calculate this.

eg

Sender                       receipient

aa@abc.com             bb@abc.com , 11@123.com , zz@xyz.com , cc@abc.com , 10@gek.com

bb@abc.com            11@123.com

I would like to have the recipient count for last 24 hours by the same sender and if it >=5 then it should be displayed.

[Greg_Deckler]

So, you can get the number of recipients like this:

Number of Recipients Column = LEN('Table'[recipients]) - LEN(SUBSTITUTE('Table'[recipients],",",""))

Not sure what you mean by show details and such. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[Anonymous]

This is going to provide the total of the receipient count in the table , but my requirement is it needs to compare with sender name .

If one sender send email to more than 5 same/different recipents in a day then the data needs to be populated on the dashboard.

[v-yiruan-msft]

Hi @Anonymous ,

Sorry, I'm not clear about the requirement "the count of recipient needs to compare with the sender name". Could you please provide one sample with screenshots? It will help us to provide you with appropriate solution.

Best Regards

Rena

[Anonymous]

Hi Rena,

As provided an example below ,i have two tables with sender and receipient details

Sender                       receipient

aa@abc.com             bb@abc.com , 11@123.com , zz@xyz.com , cc@abc.com  

bb@abc.com            11@123.com

123@123.com          100@100.com , 100@100.com,100@100.com,100@100.com,100@100.com (email sent at different times)

aa@abc.com             10@gek.com

Consider Sender aa@abc.com has sent emails to more than 5 recipients in different timings in a day , so need to create report of the sender details whose sending emails count is >=5

So i would like to know how to calculate the count of recipients for every sender in a day.

so from the above example i should get data in a report of sender details aa@abc.com and 123@123.com but not bb@abc.com

[Anonymous]

Date	From	To	Subject of email
16/04/2020  10:20 AM	aa@abc.com	11@123.com	Test
17/04/2020 3:31 PM	aa@abc.com	bb@abc.com,11@123.com	Test
17/04/2020 4:35 PM	aa@abc.com	zz@xyz.com	Test
17/04/2020 4:35 PM	bb@abc.com	11@123.com	Test
17/04/2020 5:35 PM	aa@abc.com	cc@abc.com	Test
17/04/2020 6:50 PM	123@123.com	100@100.com , 101@100.com,102@100.com,103@100.com,104@100.com,105@xyz.com	Test
17/04/2020 6:50 PM	aa@abc.com	10@gek.com	Test

To be more specific created table as example.

so would like to know how to calculate / create measure on the count of receipients for each sender.

So output expected is 

Date                 Sender name            recipent count 

16/04/2020       aa@abc.com            1

17/04/2020       aa@abc.com            5 

17/04/2020       bb@abc.com            1

17/04/2020        123@123.com          6

[v-yiruan-msft · Accepted solution]

Hi @Anonymous ，

You can get the expected results by completing the following steps:

1. Create one calculated column to get the date with format "DD/MM/YYYY"

eDate = FORMAT('sMail'[Date],"DD/MM/YYYY")

2. Create a measure as below to get the count of recipents from every sender as suggested by @Greg_Deckler 

CountofTo = PATHLENGTH(SUBSTITUTE(MAX(sMail[To]),",","|")) 

3. Create a measure to get the number of recipients in the email sent by each sender in one day

recipent count = 
VAR efrom =
    MAX ( 'sMail'[From] )
var cdate=FORMAT(max('sMail'[Date]),"DD/MM/YYYY")
return
 SUMX(  FILTER ( ALL('sMail'), sMail[From] = efrom &&FORMAT('sMail'[Date],"DD/MM/YYYY")=cdate),[CountofTo] )

Best Regards

Rena

[Greg_Deckler]

Another way to get the number of recipients:

Number of Recipients Column 2 = PATHLENGTH(SUBSTITUTE('Table'[recipients],",","|"))