Measure for two bridged fact tables with filters from one fact table based on other fact table
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!
