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

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

    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's avatar
      speedramps
      Super User
      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])
        )