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 😎
     
viviank's avatar
viviank
Resolver I
4 years ago

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