Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello!
I am working with large data sets from our companies ERP system so I'm simplifying the issue a bit but basically we have a list of jobs and those jobs each have a start date and an end date. I need to filter the jobs shown on the dashboard by picking a date and only showing the jobs that are still "open" meaning the date chosen is on or in between their start and end dates. Jobs without an end date that will be blank. Tied to those jobs are records that need to be created daily. Those records have a header record with a date and detail records. I would also like to be able to show jobs that have a record for that date, even if the job is not started yet or if it has already ended.
What i would like to be able to do is for a given single date from the header record (or related date table), pull the daily records for those jobs (which there could be multiple) and show which jobs had a record, then show all of the jobs that dont have record but should have given their start and end dates.
Expected outcome based on the data would be a dashboard that is defaulted to yesterday's date using a bookmarked filter and that will filter the number of jobs to show the total number of jobs open on that date, the total number with a record, the total number without a record, and then aggregate the data from the records in various ways like total # of workers, total internal worksers, total external workers, number of closed records, and number of pending records. There are also other tables like key job personnel that would pull in as well. Then another button will clear that filter and the user can select any date in the past. Basically the date selected should filter the job table and then the job table should filter everything else.
I have this working based on static job statuses, but we need to be dynamic so that if i look at yesterday's date it shows the jobs that were active as of yesterday and if i look at 2 weeks ago or 2 months ago it will show different jobs becuase some will have ended and others will have started.
The two items I'm struggling with are how to dynamically filter the jobs shown to only those open based on the date selected and their start and end dates while also show all jobs that have a record, and all jobs that should have a record but don't. And ideally showing them all in one table with some summary information in cards along the top.
The users of this will then filter that table (or it will be pre-filtered by RLS for them) to follow up on jobs missing records or with pending records on a given date or see aggregate data for a specific date for workforce.
Any insight into how to get this thing to filter like this would be greatly appreciated!
Using the data below if i set a date slicer for 6/4/2021 I should see a table like this.
DETAIL TABLE
Job | Number of records | Number of closed records | Number of Pending records | Number of Internal Workers | Number of External Workers | Job Manager |
100 | Peter Cottontail | |||||
200 | 2 | 1 | 1 | 5 | John Smith | |
300 | Jane Doe |
If I set it for 6/9/2021:
DETAIL TABLE
Job | Number of records | Number of closed records | Number of Pending records | Number of Internal Workers | Number of External Workers | Job Manager |
100 | 1 | 1 | 5 | Peter Cottontail | ||
200 | John Smith | |||||
300 | 1 | 1 | 2 | 5 | Jane Doe | |
400 | 1 | 1 | 7 | 5 | Mike Jones |
SAMPLE DATA
Job Table
Job Code | Job Name | Start Date | End Date |
100 | Job 1 | 6/1/2021 | 6/5/2021 |
200 | Job 2 | 6/2/2021 | 6/10/2021 |
300 | Job 3 | 6/3/2021 | |
400 | Job 4 | 6/8/2021 |
Record Header
Job Code | Record Header System Code | Record Date | Record Status |
100 | 68321 | 6/1/2021 | Closed |
100 | 68548 | 6/2/2021 | Closed |
100 | 69345 | 6/9/2021 | Pending |
200 | 68234 | 6/2/2021 | Closed |
200 | 69127 | 6/4/2021 | Closed |
200 | 69534 | 6/4/2021 | Pending |
200 | 69652 | 6/6/2021 | Closed |
200 | 69758 | 6/6/2021 | Pending |
200 | 69843 | 6/7/2021 | Closed |
200 | 69984 | 6/7/2021 | Pending |
300 | 69712 | 6/6/2021 | Closed |
300 | 69995 | 6/9/2021 | Pending |
400 | 72568 | 6/9/2021 | Closed |
400 | 73568 | 6/15/2021 | Pending |
Record Detail
Job Code | Record Header System Code | Record Detail System Code | Company Name | # of workers |
100 | 68321 | 68321.1 | Internal 1 | 7 |
100 | 68321 | 68321.2 | External 2 | 8 |
100 | 68321 | 68321.3 | External 3 | 2 |
100 | 68321 | 68321.4 | External 4 | 5 |
100 | 68548 | 68548.1 | Internal 1 | 4 |
100 | 68548 | 68548.2 | External 2 | 6 |
100 | 68548 | 68548.3 | External 3 | 5 |
100 | 68548 | 68548.4 | External 4 | 8 |
100 | 69345 | 69345.1 | Internal 1 | 5 |
200 | 68234 | 68234.1 | External 1 | 2 |
200 | 69127 | 69127.1 | External 1 | 3 |
200 | 69534 | 69534.1 | External 1 | 2 |
200 | 69652 | 69652.1 | External 1 | 3 |
200 | 69758 | 69758.1 | External 1 | 5 |
200 | 69843 | 69843.1 | External 1 | 2 |
200 | 69984 | 69984.1 | External 1 | 1 |
300 | 69712 | 69712.1 | Internal 1 | 2 |
300 | 69712 | 69712.2 | External 3 | 5 |
300 | 69995 | 69995.1 | Internal 1 | 2 |
300 | 69995 | 69995.2 | External 3 | 5 |
400 | 72568 | 72568.1 | Internal 1 | 7 |
400 | 72568 | 72568.2 | External 4 | 5 |
400 | 73568 | 73568.1 | Internal 1 | 8 |
Key Personnel
Job | Name |
100 | Peter Cottontail |
200 | John Smith |
300 | Jane Doe |
400 | Mike Jones |
Solved! Go to Solution.
Doesn't seem to be working as I expect. I was able to solve this on my own using an answer to one my past questions on this forum. Using this measure and date table that goes from 5 years ago to today:
Hi CMDATA
Thanks your example data but I dont quite understand all your explantion.
I have therefore built the attached PBIX so we can try work together to solve your problem.
Click on Edit Queries and note I have created a Calandar table and denormallised your tables all into one FACT table.
I appreciate you may not wnat to normalise you large ERP data in the final solution,
but this denormallised table will greatly help simplify the problem and better understand your maths.
We can then then use relationships in the final solution.
i.e lets walk before we can run !
Please can you explain in full detail your maths / logic for job 400 for a silcer date = 6/9/2021.
How do you get an answer of 1 please?
Thank you so much for helping me with this!
This comes out as 1 becuase there is only 1 header record for 6/9/2021 for job 400. On your flat table it would be a distinct count of header record by job.
Hi again CMDATA
Thanks for the additional infomation, I think I understand now.
Click here for the an updated solution
Note I have used Edit Queries to denormalise the data.
This make it easier to undestand and might be more efficient than having lots of releationships.
In the Field well, expand the DAX measures and look how I have created each measure.
Remember we are BI community voluntrees so please click the thumbs-up for me taking the trouble to help you and then accept the solution if it works. Thank you !
Its almost there but not quite. The missing piece is that the date selection should be all days and the report table should show both jobs with records and jobs without records as long as the job without records was active on the date selected.
Hi again CMSDATA
Click here to see demo solution
I have added a calendar slicer with a relationship to the fact table.
Have a look at the edit queries, relationships and dax measures.
Remember I don't get paid, we are BI community voluntrees so please click the thumbs-up for me taking the trouble to help you and then accept the solution if it works. Thank you !!!
Hello. Thank you so much again for working on this. That link is not working for me. your previous link worked just fine.
Doesn't seem to be working as I expect. I was able to solve this on my own using an answer to one my past questions on this forum. Using this measure and date table that goes from 5 years ago to today:
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |