Forum Discussion

Fro88er's avatar
Fro88er
Helper IV
4 years ago
Solved

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 thos...
Need Help
  • speedramps's avatar
    speedramps
    4 years ago
    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.
     
    Now please help me with kudos and click the thumbs up and accept as solution button. Thanks 😎
     
Fro88er's avatar
Fro88er
Helper IV
4 years ago

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's avatar
speedramps
Super User
4 years ago

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's avatar
    Fro88er
    Helper IV
    4 years ago

    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