Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
MightyMicrobe
Helper II
Helper II

Filter Two Fact Tables

I have two fact tables that are related to a dimension table, like so:

'Campaign Clicks'  *<--1 'Customers' 1 --> * 'Inbound Calls'

 

I need to build a measure showing the average number of inbound calls per customer (total calls/total customers) for the customers who called in within 7 days of clicking on the campaign. 

 

I cannot change the direction of the relationships or create bridging tables, so everything will have to be coded in measure. I tried using TREATAS, but it does not give me the results I need. Propagating filters using TREATAS in DAX - SQLBI

 

Any tips?

6 REPLIES 6
v-jingzhang
Community Support
Community Support

Hi @MightyMicrobe 

 

Can you provide some dummy data about the three tables as well as the expected result? You want to count the customers who called in within 7 days of clicking on the campaign, do they only click on the campaign once? How many campaigns are included in 'Campaign Clicks' table? Sample data will help us understand more about what you want. 

 

Best Regards,
Community Support Team _ Jing

Hi @v-jingzhang thanks for picking this up. 

Below is the screenshot of the model part in question, the three tables are linked on Customer ID, but there is no relationship between the two fact tables. 

MightyMicrobe_0-1639003085790.png

One customer can click on an unlimited number of campaigns, but for simplicity sake, I'm working with just the latest episode of clicks/calls, so something like MAXX will do. 

Hi @MightyMicrobe 

 

It's still not very clear. Can I consider that in 'Campaign Clicks' table, we need to get the latest time of a click for each customer. If a customer made a call/calls after that time, we need to extract the earliest call time and calculate whether the duration between "latest click time" and "earliest call time" is less than 7 days? If it is less than 7 days, then we count this customer and count how many calls he made from 'Inbound Calls' table?

 

If my above understanding is correct, is it possible that a customer would click campaigns again after he had made a call? If so, only getting the latest click time is not appropriate as it would miss calls happened earlier than that. 

 

Can you provide some sample data to help me understand the problem better?

 

Best Regards,

Jing

Hi @v-jingzhang below is the data samples from the campaign and call tables, I hope it makes sense. 

The business problem I'm trying to solve is a reduction of incoming calls. If a customer sees an online campaign they should ideally resolve their issue online instead of calling in. But some campaigns drive up call traffic instead. 

So the questions I'm trying to answer are these:

  1. How many customers called within 7 days of clicking on a campaign. 
  2. How many calls did they make in total (that's a straight row count on the calls table).
  3. Which campaign codes are successful (i.e. clicks resulting in the least call traffic). 

I hope it makes sense, thank you for your help. 

 

CAMPAIGN CLICKS

Customer IDCampaign IDDateCampaign Clicks
12345ABCDE2021-12-010
16582ABCDE2021-12-012
12345AEEEE2021-12-021
12345ABCDE2021-12-035
17777EEFFF2021-12-048

 

INCOMING CALLS

Customer IDDateCall TopicResolved
123452021-12-07SupportTRUE
995982021-12-07ComplaintTRUE
123452021-12-08SalesTRUE
amitchandak
Super User
Super User

@MightyMicrobe , Try a measure like

 

 

Measure =
var _min = calculate(Min('Campaign Clicks'[Start DAte]), allexcept(Customers[Customers]))
return
calculate(sumx(values(Customers[Customers]) , count('Inbound Calls'[Call ID]), Filter('Inbound Calls', 'Inbound Calls'[Date] >= _min && 'Inbound Calls'[Date] <=_max)))

Sorry, this does not work. The last FILTER in the RESULT clause trips it up. 

MightyMicrobe_0-1637620503867.png

 

 

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.