Dates & Order No Relationships

Hi guys,
To start with, i think that the answer to my question is that i have set up my data model incorrectly, but am hoping that someone will be able to shed light on what i need to do to rectify it.
I am working with two fact tables. One describes customer enquiries about a service and the other describes the orders. The commonality between these tables is an order number. I have imported a DimDate table and have joined it to the enquiries via the date that the enquiry was lodged whereas in the order table the DimDate table is joined to the date that the job was closed. Any job number that is in the order table is also in the enquiries table, but the reverse is not true if a customer decided that they did not want to take up the service.
I can report on enquiries and orders seperately, but not together. I believe that iā€™ve gone wrong by not creating the relationship between the two tables using the order number, but based on this iā€™m not now sure how to incorporate the DimDate table, given the need to report on different date fields. Iā€™m thinking that the video on creating a relationship with more than one date may help but wondered if anyone could give me some pointers.
Richard.

It does sounds like these are both fact tables. So that parts correct.

To me though you need to build a better model around this.

The order number is a good start, but there must be other common columns as well like customers for example.

You need to somehow break this out and add it to the model, then you will be able to filter by a customer and both fact tables will filter also based on the relationships you have.

If you can add some images that is always helpful as well to really know whatā€™s going on here, but to me this is definitely about getting the right model in place and then everything should be easy off the back of that.

Hi Sam,
Iā€™ve attached an image of my data model. You should see the two fact tables (Job Status & Invoice Match). My DimDate table joins to the Job Status table via the ā€˜Date_Job_Lodgedā€™ field and to the Invoice Match table via a derivative of the ā€˜Date_Job_Closedā€™ column.
Things that i have tried so far (which failed) are to create a second DimDate table, create an indirect relationship between the fact tables and use the ā€˜USERELATIONSHIPā€™ DAX formula and also to create a bridging table, containing the job number.
In essence, if i create a chart which shows the number of enquiries (from the Job Status table) and completions (from the Invoice table), this illustrates the correct values. However, when i try to filter use (for example by the Job_Order_Trade field in the Job Status table), the number of enquiries displays correctly, but the number of completions does notā€¦presumably because the relationship is not there.
I hope that this makes sense.

Iā€™m pleased to say that i think that iā€™ve fixed it. Iā€™d be interested to know your thoughts. Iā€™ve added a screenshot of my new data model below.
I appeared to be on the right lines earlier when i said that i experimented with importing in a second DimDate table.
So iā€™ve created a second DimDate table, which is a carbon copy of the original, albeit with a different name. So whatever changes i make to the original DimDate table will be reflected in the second.
What i failed to get right earlier was the relationship between the two fact tables. Iā€™ve now set this as a bi-directional relationship. To be honest, i donā€™t completely understand how it works, but it gives me the correct output and my filters work correctly (for now).

Hoenstly Iā€™m not really a fan of this setup with the relationship between the two fact table.

This really shouldnā€™t be required if these are truly fact table.

Also why do you think you require to date table? Iā€™m not really sure that you do in this case.

Also you budgets are likely a fact table as well so I would place that on the lower level.

I think you first model looked better. Just the budget table adjustment down to the lower level.

As I donā€™t know the data that well here I can only throw up suggestionsā€¦

Why arenā€™t you able to create a relationship between the members table and the jobstatus table?

You should always attempt to have your main filter come from lookup tables, so if there is common information in both fact tables, the idea is to break out that data (columns) and turn it into a lookup table that can filter down to the fact tables.

Hereā€™s some ideas and concepts to review.

Hi @richard3butler,

I believe I had a similar situation to yours with a report for a ticket and event-based system. Tickets and Events are the two fact tables, and Events is a child of Tickets. Tickets and Events are joined on the TicketID. A slight difference with your description is that I will always have at least one event for each ticket.

I tried to do a single Dates table originally, but with the two fact tables already joined on the TicketID I could not join the Dates table to both the Tickets and Events tables (I believe the error was about an ambiguous relationship). I needed to join to Tickets.TicketDate and Events.EventDate (similar to your situation with two different date fields).

After some digging online and into Enterprise DNA videos, I found the simple answer which was to create a copy of my Dates table, rename them both (TicketDates and EventDates), and then join each to the applicable date field.

Iā€™m not claiming this is perfect as I am about 2 years part-time into Power BI, but it did solve all my problems and I now have a very powerful report that completely replaces a traditional data warehouse plus SSAS cube plus Excel PowerPivot workbook. Thanks to Sam for all the great training which I will continue to consume!

Enterprise%20DNA%20Expert%20-%20Small

Thanks Sam and @jpratt for getting back to me. Iā€™ve been on leave for the past few days so only just got back to looking at this. Sam, the reason why i took this approach was that i needed some way of being able to link the two fact tables by job number as i couldnā€™t see any other way to do it. I wanted to avoid having two date tables in my model, but i had no other option but to do that once i linked the two fact tables.
Youā€™re also quite right in saying that my budget table is a fact and should be on the lower level. My bad. For now iā€™d ask you to ignore the member table. This is one of the first projects that i have done in Power BI and this is something that iā€™ll look to address in future.
As i mentioned before, the only reliable lookup value between the two fact tables is the job number (there is also the date_job_lodged and date_job_closed columns, but iā€™m already creating relationships using them). However, if i create a lookup table of unique job numbers from the enquiry table (JobStatus), and then set my relationships using that, i get an indirect relationship between that and the invoice table. Is that what you would expect? Also, as every job generates a unique job number i would expect the relationship to be 1:1, yet i canā€™t select that in the data model. Maybe, and this is something that has just popped into my head, given that there is a 1:1 relationship i should be looking to perform a join so that i have one big fact table??

@jpratt, thatā€™s one heck of a data model, but yes it appears to be very similar to mine.

Ok sounds good. Thanks for clarifying.

Hi Sam,

Iā€™m sorry to bring this one back up but i ended up having problems with the model that i created and after investigating the relationship between the fact tables itā€™s not a true 1:1 relationship (iā€™ve encountered a handful of enquiries that have the same job number) so i canā€™t join the tables. Sam, iā€™ve been through all the videos in the section that you highlighted and i canā€™t apply it to what iā€™m trying to achieve.
Letā€™s take it back to basics. Iā€™ve shown my amended data model below.
In the current setup, i have created a series of measures for each table. In the JobStatus table there is a field for Job_Enquiry_Source (circled). This explains how the job was received (web, phone, etcā€¦). How should i go about filtering the number of completed jobs from the Invoice Match table according to each source? If i can figure that out, then it will help me to work through other filters that i want to apply that are unique to each table.

If this is a really simple solution and iā€™ve wasted peopleā€™s time then i apologise.

First why do you have a multi direction relationship from your date table to your budget table.

Seems odd and doubt you need this. It should be a one to many relationship.

There should never be any relationships between fact tables, so you need to find a different way to bring things all together in your model.

The way to fix it is usually to create a new lookup table that can filter down to both fact tables. Then you derive your filters from that new table.

What common column do you have in both those tables that can be turned into a unique lookup table? Is it JobStatus Code or something like that? Or Job number?

Whatever it is, it needs to become a lookup table where there is a column of unique values.

Have you had a chance to review these course modules?

So crucial if you havenā€™t yet, it should all become clearer what youā€™re trying to achieve on this one.

Chrs

Hi Sam,
Iā€™ve watched these videos multiple times and whilst i feel that i understand the context of them, applying them to my situation is less clear.
I have created a lookup table of job numbers from the JobStatus table. However, i donā€™t understand what you mean when you say ā€œThen you derive your filters from that new tableā€. Am i physically adding something to that lookup table or creating another table from this.

If itā€™s ok with you, letā€™s ignore the budget table for now. Hereā€™s my amended data model.

Ok thatā€™s great. Glad youā€™ve reviewed these video.

Now the key to this I guess (that even I donā€™t know because I can see your reports) is what visual are you trying to show.

What calculations are you looking to represent and then how do you want to filter them.

When thinking about what to turn into a lookup table (i just presumed job number) you have to thinkā€¦how do I want to filter my calculations from these fact table.

Is it something to do with a particular job?

Is it just time related? Which the date table takes care of.

Thatā€™s the key.

You want all filtering of information to be complete centrally via lookup tables.

In saying this whatā€™s an example of something you want to show in a report? that works across both this tablesā€¦

I guess the other interesting thing when I drill into the image of these tables. They actually looking quite similar, they have quite similar columns.

Therefore it seem you should have more in your lookup table that just Job_Numberā€¦maybe you should include all the common column o the two tables like Job_Status, Client_Type etc.

Instead of repeating these common column across the two table these should be taken up to the lookup table layer and then joined down to the fact table via a one to many relationship.

Has this given you some idea?

Let me know.

Thanks

Hi Sam,

So i feel like a bit of a douche in writing this, but iā€™ve been able to work through my problem. First of all i need to say thank you for persisting with me!
In essence i was trying to find the answer to a question that i shouldnā€™t have been asking. For a number of days was able to apply a filter from fact table 1 to fact table 2 and what i was trying to do was the reverse; apply a filter from fact table 2 to fact table 1. Where i went wrong was that as fact table 2 is basically a subset of fact table 1 (figuratively, not literally), trying to apply a filter from it offered no value whatsoever, unless i just wanted that filter to apply to fact table 2.
But what this has taught me is how i should organise my data model if no dimension tables exist. Sam, your videos were valuable (as always), although i ended up creating my lookup table in the Power Query Editor (by creating a reference table from fact 1, removing any columns from that which i did not want to filter and then removing all duplicate job numbers). Iā€™m not yet sure how i would do that in DAX.
Either way, i trust that my data model is now better suited towards conducting some meaningful analysis. Thank you once again.
Richard.

1 Like

Nice one.

Iā€™m convinced you would have learnt a whole bunch from working through this.

Using the query editor the way have is the perfect way to manage it.

Chrs
Sam