I am having an issue filtering Client Submits on Jobs using ONLY the Job date added.
We have two tables. Jobs and Client Submits. JobID and when it was date added, then Clients submitted to those Jobs with a ClientSubmit date. I am joined on JobID. The ask is to count the Client Submits in a date range of the Job date range. Client Submits could be weeks/month after the Job was added/posted.
Scinerio, how many Jobs (count) did we have last month, and the clients submits (count). Not all client submits, just the ones last month/date range selected.
I have included a Link to my PBIX file and the Client Submission Userelationship measure.
Desired outcome: in this scinerio there are Client Submits in May that I need excluded, because they don't fall within the Date Range.
Solved! Go to Solution.
Thank you Fro88er for accepting the solution. 😁😁😁
I had a look and think that the FactJobs table is not required because all the fields are in FactJobSubmission.
Raise another ticket if you need help resoving the the Fact to Fact relatioinship and quote @speedramps in the ticket. That will automtaically send me an notification but another solver might get to you first.
Good luck ! 😎
Here's how I would do this. Change the model so that you have all the Facts with Dates in one table. Combine the Adds and the Submits into one table. It will look like this.
Job Id Date Category
1 1/1/2022 Added
1 1/6/2022 Submitted
2 2/2/2022 Added
2 2/5/2022 Submitted
In this example, Job 1 was added on Jan 1 and then submitted on Jan 6. It's on two rows now.
To get this, you use Power Query.
In FactJobs, I added a Custom Column called "Category" and set it to "Added". Then I renamed the dateAdded field to date.
In FactJobSubmission, it already has a date column, so I removed dateAdded (it's the same thing) and added a Category column and marked it Submitted.
(You want the column names to be the same so you can combine the tables.)
In Power Query, Append FactJobs and FactJobSubmission as a New query. Then uncheck Enable Load for FactJobs and FactJobSubmission.
In the model, you connect your DimCalendar table to the one Fact table, like this:
Then to get a count of how many jobs were added or submitted in a time period, you use COUNTROWS and you use CALCULATE to filter to show the ones that were added or submitted.
A link to the updated PBIX is here:
Hi @Fro88er ,
Did the above suggestions help with your scenario? if that is the case, you can consider Kudo or accept the helpful suggestions to help others who faced similar requirements.
If these also don't help, please share more detailed information to help us clarify your scenario to test.
You need to use "role play" inactive relationships.
They are called role play because you one for Jobs Added and another for Client Submissions.
I helped you now please help me with kudos.
Please click the thumbs up and accept as solution button.
Thanks ! 😎
This is helpful thanks, however this is not a Job date vs Submission date. Is it possible to filter both dates simultaneously? Meaning I have Job and Job date. I also have Job Submission and Submission date. When I filter the Job Date (active relationship to date calendar), it filters the Jobs in that date range (good), and I also need the Job Submissions in that date range only on those Jobs. Basically, I only want to see submissions on those jobs in that range. Currently, I am pulling submissions on those jobs past the date range, which I don't want.
I see where you are going. Unfortunatly, this solution does not work because there is an inactive relationship between Jobs and JobSubmits by JobOrderID (you can see this in the PBIX file I shared). Both have dates, the primary being Jobs is the one joined to the date calendar. I tried the below and is not returning a value, but also not throwing an error.
You should use a star schema
with 1:M relationships from Dim to Facts.
Do not have relationships from Facts to Facts.
That is a fundamental rule of BI data modelling.
Fact to Fact relationships are after often a sign that someone has done it in desperation to fix one problem. But they come back and bite you and cause another problems elsewhere.
I think we are now going off track from the orignal problem.
The role play is the tried and trusted correct solution for the orignal problem and we should not be alrtering that because the Fact to Fact relationship is wrong.
I suggest you accept that role play solution and delete the Fact to Fact relationship and try find another way to do that particular functionality.
If you need help with that particular functionality then raise a new ticket explaining what that functionality is for.
I think we are done with the original role play solution and would apprecaite if you accept the solution and we move onto the next problem.
One problem per ticket will get you a quicker repsonse and given each solver kudos.
Then everyone is happy.
I appreciate your time on this subject. Yes, I am trying to relate one fact to another. They (jobs and submits) are both fact tables, and related to the calendar correctly to calculate Jobs and Submits independantly. (I only shared a small sub-set of the larger model for privacy reasons.) I was hoping I could squeek out / hi-jack the existing submit data in a calculation to get what I needed. Mostly likely I will duplicate the submits table and join right to the Jobs table and follow your recomendation. Thanks again, M
Check out the November 2023 Power BI update to learn about new features.
Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.
130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.