Help! DAX to calculate percentage of e-mail campaign visualization

[LeandroCampacci]

Hello!

I need a help, and sorry for my bad english.

I'm making a dashboard to show the reading rate of communications sent by email and I found a difficult to apply a correct rule.

Explaining:

I have a table that contains this information (examples):

Date	Campaign	Event	User
06/29/20	AAA	Participated	ana@example.com
06/29/20	AAA	Viewed	ana@example.com
06/30/20	BBB	Participated	ana@example.com
07/01/20	BBB	Viewed	ana@example.com
07/01/20	CCC	Participated	ana@example.com
07/03/20	CCC	Viewed	ana@example.com

Note that in the example above there are three campaigns (AAA, BBB and CCC). The date of sending each one of them is related to the "Event" column with the key "Participated". Thus, the AAA, BBB and CCC campaigns were sent on 06/29, 06/30 and 07/01 respectively.

The reading of these campaigns, however, is not always on the same day. See that the AAA campaign was sent and viewed on the same day (06/29). The BBB campaign was sent on 06/30, but was viewed the other day, already in July, on 07/01. The CCC campaign was sent on 7/1, but was viewed on 7/3 - three days later.

For each campaign, I can calculate the number of users participating without problems. But I found it difficult to calculate the reading percentage, especially if I have a related date table in which the dashboard user can filter a period of one month.

For example: if I filter only July, the user ana@example.com would have 200% view (CCC, correct; and BBB, read a day later).

Is there a way for me to measure the percentage of viewing according to each release (without having to filter them) and also measure the preview of the first day of submission, second day, third day, etc.?

[v-yiruan-msft · Accepted solution]

Hi @LeandroCampacci ,

You can create 2 measure as below to achieve it:

Count of Viewed = 
var _viewCount=CALCULATE(DISTINCTCOUNT('Campaigns'[Campaign]),FILTER('Campaigns','Campaigns'[User]=MAX('Campaigns'[User])&&'Campaigns'[Event]="Viewed"))
var _tCount=CALCULATE(DISTINCTCOUNT('Campaigns'[Campaign]), FILTER('Campaigns','Campaigns'[Event] ="Participated"))
return DIVIDE(_viewCount,_tCount,0)

Read after N days = 
var _parti=CALCULATE(MAX('Campaigns'[Date]),FILTER(ALL('Campaigns'),'Campaigns'[User]=MAX('Campaigns'[User])&&'Campaigns'[Campaign]=MAX('Campaigns'[Campaign])&&'Campaigns'[Event]="Participated"))
 var _viewed=CALCULATE(MAX('Campaigns'[Date]),FILTER(ALL('Campaigns'),'Campaigns'[User]=MAX('Campaigns'[User])&&'Campaigns'[Campaign]=MAX('Campaigns'[Campaign])&&'Campaigns'[Event]="Viewed"))
return DATEDIFF( _parti,  _viewed,DAY)

Best Regards

Rena

[LeandroCampacci]

Thanks, @v-yiruan-msft! The second measure (Read after N days) worked here.

One question: is there a way to measure ONLY campaigns sent within the filtered period (july, for example)?

Because, instead of having 200% view (adding a campaign sent in June, but read in July), I could only have 100% of July.

[v-yiruan-msft]

Hi @LeandroCampacci ,

Whether the problem has been resolved? If yes, could you please mark the helpful post as Answered? It will help other members in the community find the solution easily if they face the similar problem with you. Thank you.

Best Regards

Rena

[LeandroCampacci]

Sorry for the delay and thanks for the help.

I managed to go here in two ways.

First, using @v-yiruan-msft dax solution. My original table had many duplicate results, but it was possible to make some adjustments using Power Query.

Another way that worked was using the expression SUMMARIZE to create a new virtual table, which greatly facilitated the understanding of my original table.

[v-yiruan-msft]

Hi @LeandroCampacci ,

I'm not very clear about your requirement... Could you please explain more about the below requirement?  What you want is to get the number of campaigns which sent in the filtered period? Please provide your expected result using some examples and figures.

---

One question: is there a way to measure ONLY campaigns sent within the filtered period (july, for example)?

Because, instead of having 200% view (adding a campaign sent in June, but read in July), I could only have 100% of July.

---

Best Regards

Rena

[amitchandak]

@LeandroCampacci , Not very clear. Do you have a send date separately? all data will be analyzed based on the send date of the campaign for campaign effectiveness. When you do not effectiveness, you can use Participated or viewed date

[Greg_Deckler]

Sorry @LeandroCampacci - I'm not quite following what you want as output. Please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, 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

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.