Measure for two bridged fact tables with filters from one fact table based on other fact table

[ekulchewie]

Hi all,

I have the following model which involves 2 fact tables and 2 dimension tables:

Fact Table A = table of enquiries with a date of enquiry and other related dates about the enquiry e.g. Date enquiry dealt with, date of next enquiry. Many enquiries relate to a single customer, who has one phone number. (named 'SQL_Enquiries')

Date of Enquiry	Phone Number	Min call date window	Max call date window	Contact Number	Enquiry Number
15/01/2024	779124623		18/01/2024	123456	1
16/01/2024	779124623	18/01/2024	24/01/2024	123456	2
17/01/2024	776634661		25/01/2024	818654	1

Fact Table B = table of phone calls which has a column for date of call and caller phone number (named 'Compiled Call Log')

Call Time	Phone Number
14/01/2024  15:39:00	779124623
15/01/2024  08:30:00	779124623
21/01/2024  10:31:00	776634661

Dim Table A = date table which is linked via 2 1:many active relationships to A & B’s date of enquiry/call

Dim Table B = list of phone numbers also linked via 2 1:many active relationships to A & B via the phone number columns

Phone Number

779124623

776634661

I have a couple of calculated columns in table A which evaluate the maximum call date and the minimum call date to establish a range of call dates that ‘could’ relate to a phone call to use as a filter.

I am looking to create a measure to evaluate the number of phone calls made related to each enquiry (via the phone number) filtered by whether the call date falls into the maximum / minimum range of the enquiry date.

I’ve managed to do this as a calculated column in table A with the following DAX, but I cannot get this to work as a measure - any help appreciated please!

Note - I could join fact table A and fact table B to go with a proper star schema but I don’t wish to filter all existing measures for enquiries based on whether they are an enquiry or a call.

Thanks!

[123abc]

To create a measure that evaluates the number of phone calls made related to each enquiry (via the phone number) filtered by whether the call date falls into the maximum/minimum range of the enquiry date, you can use DAX functions to calculate this dynamically. Here's how you can approach it:

Calls Related to Enquiries =
VAR EnquiryPhone = 'SQL_Enquiries'[Phone Number]
VAR MinCallDate = 'SQL_Enquiries'[Min call date window]
VAR MaxCallDate = 'SQL_Enquiries'[Max call date window]
RETURN
CALCULATE(
COUNTROWS('Compiled Call Log'),
'Compiled Call Log'[Phone Number] = EnquiryPhone,
'Compiled Call Log'[Call Time] >= MinCallDate,
'Compiled Call Log'[Call Time] <= MaxCallDate
)

Explanation:

• We first define variables to store the phone number, minimum call date, and maximum call date for each enquiry.
• Then, we use the CALCULATE function to apply filters to the 'Compiled Call Log' table:
  • Filter by 'Phone Number' to match the enquiry phone number.
  • Filter by 'Call Time' to make sure it falls within the range defined by the minimum and maximum call dates for each enquiry.
• Finally, we count the rows in the 'Compiled Call Log' table that meet all the specified conditions.

You can create this measure in your model and use it in your reports to get the count of phone calls related to each enquiry based on the specified date range.

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

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

[ekulchewie]

Hi, thanks for this! I understand the approach trying to be used here, but the measure won't work without there being a single value for the variables defined, I tried using SELECTEDVALUE() around the 3 variables but that doesn't seem to do the trick - is there another function I can try to get it evaluate each enquiry row in turn? Thanks!

[123abc · Accepted solution]

To handle scenarios where there isn't a single value for the variables due to the iteration over rows in 'SQL_Enquiries', you can use the SUMX function along with FILTER to iterate over each row of 'SQL_Enquiries'. Within the iteration, you can calculate the count of related phone calls for each enquiry. Here's how you can modify the measure:

Phone Calls Related to Enquiries =
SUMX(
'SQL_Enquiries',
VAR CurrentEnquiry = 'SQL_Enquiries'[Enquiry Number]
RETURN
CALCULATE(
COUNTROWS('Compiled Call Log'),
FILTER(
'Compiled Call Log',
'Compiled Call Log'[Phone Number] = EARLIER('SQL_Enquiries'[Phone Number]) &&
'Compiled Call Log'[Call Time] >= EARLIER('SQL_Enquiries'[Min call date window]) &&
'Compiled Call Log'[Call Time] <= EARLIER('SQL_Enquiries'[Max call date window])
)
)
)

Explanation:

• SUMX iterates over each row of 'SQL_Enquiries'.
• EARLIER function is used to refer to the values of columns from the current row context created by SUMX.
• Within the CALCULATE function, FILTER is applied to filter the 'Compiled Call Log' based on the current row's Phone Number and date range (Min call date window and Max call date window).

This modification should allow the measure to correctly evaluate the count of phone calls related to each enquiry, considering the date range conditions for each row in 'SQL_Enquiries'.

If I answered your question, please mark my post as solution, Appreciate your Kudos.

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

[ekulchewie]

Amazing - thank you very much!

Am I correct in assuming the variable declared is for improved readability to understand the SUMX is iterating for each record?

Many thanks