Hi all,
I have a table called Campaigns that has a list of all campaigns and another table called Distinct Last Touch that only shows campaigns that have revenue attributed to them. See image #1.
I have funnel diagram, see image #2, where I have four metrics. The first metric Total # of Campaigns is based on the Campaigns Table. When I filter from the filter pane, it doesn’t count the aggregate but only count the total from the Distinct table.
I tried to create a new filter measure using IsFiltered but it is not filtering correctly. See image #3 for the new measure.
Can someone help? I hope I am making sense. The end result I am trying to achieve as an example is in image #4 if. I was to select Vaccine from the drug type filter. the Total # of campaigns should be 17. The remaining metrics are correct.
Image#1
Image #2
Image #3 of modified filter measure
Image #4 - Expected end result
WIP-PQ Campaign Test v.2.pbix (8.5 MB)
@ysherriff The problem with your scenario is Distinct Last Touch Campaign Id has only 4 campaign ID for Vaccines whereas Capaigns table has 17 distinct ID. Since you are using the field from Distinct there are only 4 ID that participate in the relationship.
So you need to first grab the Drug Type available in the filter context, apply it to the Capaign table and also remove the filter from the Distinct Table.
WIP-PQ Campaign Test v.2.pbix (8.5 MB)
Total Campaigns =
VAR _SelectionDate =
SELECTEDVALUE ( 'Date'[Year] )
VAR SelectedDrug =
SELECTEDVALUE ( 'Distinct Last Touch Campaign Id'[Drug Type] )
VAR Result =
SWITCH (
TRUE (),
_SelectionDate = BLANK (),
CALCULATE (
DISTINCTCOUNT ( Campaigns[Campaign Id] ),
Campaigns[Drug Type] = SelectedDrug,
REMOVEFILTERS ( 'Distinct Last Touch Campaign Id'[Drug Type] )
),
CALCULATE (
DISTINCTCOUNT ( Campaigns[Campaign Id] ),
USERELATIONSHIP ( Campaigns[Campaign Start Date], 'Date'[Date] )
)
)
RETURN Result
1 Like
Thanks @AntrikshSharma for the solution but what happens if i have multiple filters in the filter pane.
For instance, in the workbook you provided, I have Marketing Solutions SubSegment as well. Should I use Isfiltered?
Hello @AntrikshSharma,
I hope this message finds you well. I wanted to take a moment to thank you for your help on this post - your contributions to our community are truly appreciated!
At this point, it’s been 9 days since the original post, and we’re wondering if we can go ahead and close this thread. If you are still actively working on this inquiry, please let us know, and we’d be happy to keep the thread open.
Otherwise, we suggest closing the thread to keep our forum organized and ensure that we are providing timely assistance to our community members. Of course, if you or anyone else has any further questions or concerns, please don’t hesitate to reach out.
Thank you again for your help, and we hope to hear back from you soon!
Best regards,
