Madison Power BI Apr 2022 - Junk Dimensions
In dimensional modeling, dimensions are often a tangible thing: the date, the time, the customer, the employee, the location, the product. It's something you can see and point to; even have a conversation with. But what happens when your dimension isn't a tangible thing? When it's just a collection of random pieces of information? How do you model things that aren't things? This is where you enter the murky world of junk and degenerate dimensions. Let's talk about why junk might be exactly what your model needs. PLUS, if you have your own Power BI/star schema modeling questions or challenges, bring them to share with the group and let's see if we can't help. --- The meeting will start at 6pm and end at 7.30pm on Wednesday April 6th. We'll have pizza and soft drinks. We do ask that you wear a mask. ***NEW LOCATION*** We'll be meeting on the ground floor of the Hy Cite offices in Middleton: 3252 Pleasant View Rd, Middleton, WI 53562. It's on the far west side - exit the beltline on Airport Rd (exit 250). Hy Cite is on the corner of N Pleasant View Rd and Airport Rd just before Airport Rd goes from 2 lanes to 1. The main entrance is on the Airport Rd side of the building. What3Words: ///trees.laser.boat --- Unrelated, if you are looking for (or are open to) a new position that uses your Power BI skills, get in touch! We’re aware of at least 2 companies hiring in the Power BI space right now.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!
