Filter two dates and userelationship

[Fro88er]

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. 

[speedramps]

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 ! 😎

[viviank]

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

[v-shex-msft]

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

[speedramps]

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 ! 😎

[Fro88er]

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.

[speedramps · Accepted solution]

If you want the total of jobs started and submitted in the same mdate range then

create 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 context

VAR maxdate = MAX('Calendar'[Date])

//create subset of records in the date range

VAR mysubset = FILTER( yourtable, yourtable[Submit date] <= maxdate)

RETURN

// get total for subset

COUNTROWS(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 visual

Here 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.

Click here to download an example 

Now please help me with kudos and click the thumbs up and accept as solution button. Thanks 😎

[Fro88er]

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.

Client Submission - Dis 2 =

VAR _MaxDate = MAX(DimCalendar[Date])

Return

CALCULATE([Client Submission],

FILTER(FactJobSubmission, FactJobSubmission[ClientSubmissionDateMod] <= _MaxDate),

USERELATIONSHIP(FactJobSubmission[jobOrderID],'FactJobs'[jobOrderID])

)

[speedramps]

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.

[Fro88er]

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