How to apply a calculated slicer only on one line in a line chart?

[Pley92]

I have the following business case: a company has begun an email marketing campaign to entice its customers to put more orders in. In order to verify if the email campaign is having an impact, customers have been divided into controls and targets: controls will not receive any emails, whereas targets will. 

The variable that we want to monitor over time is the average number of orders that customers have made , split (Legend) by whether they are controls or targets. For this, I have chosen a line chart.

Next, we want to be able to filter (Slicer) by the weekly average number of emails received by each customer: the rationale behind it is that, if the marketing campaign works and we report only those targets who have received a high average number of emails per week, the distance between the control line and the target line should increase.

To do that, I have created a calculated column with the following logic (please refer to the PBIX that I've linked for the DAX formula): sum of all emails received by each customer / (latest update - customer join date) * 7.

The problem is that, if I use this calculated column as a slicer, as soon as the lowest limit is raised from 0, the control line disappears; this is because controls, by definition, controls do not receive emails. To illustrate even better:

Before

After

So my question is the following: how do I create a slicer so that it only filters the target line, but not the control? The control line should remain always the same, whereas the target line should filter based on the range specified in the slicer.

Please notice that I have also included a slicer on JoinDate: this slicer should keep on working on both the control and the target lines.

As I've mentioned, I am linking the PBIX file (click on the link and then the Download arrow). 

Many thanks!

[v-tangjie-msft · Accepted solution]

Hi @Pley92 ,

According to your description, here are my steps you can follow as a solution.

(1)  My test data is the same as yours.

(2) We can create a table and a measure.

Table 2 = SUMMARIZE(RawData,RawData[AverageWeeklyEmails])

Measure = 
IF(
    OR(
        ISFILTERED('Table 2'[AverageWeeklyEmails]) = FALSE(),
        SELECTEDVALUE(RawData[ControlTargetStatus]) = "Control"
    ),
    AVERAGE(RawData[NoOfPurchaseOrders]),
    IF(
        SELECTEDVALUE(RawData[ControlTargetStatus]) = "Target",
        CALCULATE(
            AVERAGE(RawData[NoOfPurchaseOrders]),
            FILTER(
                RawData,
                [AverageWeeklyEmails] in VALUES('Table 2'[AverageWeeklyEmails])
            )
        )
    )
)

(3) Then the result is as follows.

Best Regards,

Neeko Tang

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

[Pley92]

Thank you very much for the solution; this was exactly what I was looking for!