Forum Discussion
Filter two dates and userelationship
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.
- If you want the total of jobs started and submitted in the same mdate range thencreate an active 1:M relationship from Calendar[date] to yourtable[Submit date].Then assuming that submit dates cant be less that the Job date,create this measure ....Added and Submitted =// get max calendar dates for the contextVAR maxdate = MAX('Calendar'[Date])//create subset of records in the date rangeVAR mysubset = FILTER( yourtable, yourtable[Submit date] <= maxdate)RETURN// get total for subsetCOUNTROWS(mysubset)Drag the calender date to your slicer or the table visual (dont use the yourtable dates).Then drag Added and Submitted to your table visualHere is an example using Order Date and Despatch Date with inactoive relationships but the logic is exactly the same as Job Added and Client Submitetd date. Plus it is much better for me to teach you how to DIY, rather than just build a solution.Now please help me with kudos and click the thumbs up and accept as solution button. Thanks 😎
9 Replies
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:
https://1drv.ms/u/s!Asnj1wbkvlaggfRghimuYzYUb_XJNw?e=hKaxgq
You need to use "role play" inactive relationships.
They are called role play because you one for Jobs Added and another for Client Submissions.
Please click here for an example solution and instructions
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.
- If you want the total of jobs started and submitted in the same mdate range thencreate an active 1:M relationship from Calendar[date] to yourtable[Submit date].Then assuming that submit dates cant be less that the Job date,create this measure ....Added and Submitted =// get max calendar dates for the contextVAR maxdate = MAX('Calendar'[Date])//create subset of records in the date rangeVAR mysubset = FILTER( yourtable, yourtable[Submit date] <= maxdate)RETURN// get total for subsetCOUNTROWS(mysubset)Drag the calender date to your slicer or the table visual (dont use the yourtable dates).Then drag Added and Submitted to your table visualHere is an example using Order Date and Despatch Date with inactoive relationships but the logic is exactly the same as Job Added and Client Submitetd date. Plus it is much better for me to teach you how to DIY, rather than just build a solution.Now please help me with kudos and click the thumbs up and accept as solution button. Thanks 😎
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.
How to Get Your Question Answered Quickly
Regards,
Xiaoxin Sheng
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 ! 😎
