Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I'm trying to count the number of products finished on each manager's time shift. Problem is each manager's shift changes every day.
I have two tables containing the following:
Table 1: Manager Schedule
Contains the shift schedule 2 shift managers per day
https://www.dropbox.com/scl/fi/4qb4rdo43teyew7yp0x07/Manager-Sample-Data.xlsx?dl=0&rlkey=gk8xt5cgyqs...
Table 2: Production Schedule
List of products and time when the product was made.
https://www.dropbox.com/s/7jh2hcycutk0zra/Schedule%20Sample%20Data.xlsx?dl=0
pbix file containing the two tables and date table:
https://www.dropbox.com/s/mh1146kycg2hq1k/Product%20Count%20Based%20On%20Shift.pbix?dl=0
The output should look like this:
Appreciate if you can help me out. Thanks!
Best regards,
Mark V
Solved! Go to Solution.
My bad, I misunderstood.
I kept the same changes mentioned above and added a new measure and created new visual. Please see if this helps
a) Created a measure part of manager schedule
Prod Count =
var _d1 = Minx('Manager Schedule', 'Manager Schedule'[Start Time])
var _d2 = Minx('Manager Schedule', 'Manager Schedule'[Clock Out])
var _Prod_count = CALCULATE( COUNTROWS('Production Schedule'), FILTER('Production Schedule', 'Production Schedule'[Date Finished] >= _d1 && 'Production Schedule'[Date Finished] <= _d2))
return _Prod_count
Hi,
Since you want the output to be date wise, I am assuming that the time stamp appearing in each of the 2 tables is not required.
@Ashish_Mathur
Hi Ashish,
Thank you for your reply.
For the output, time stamp is not required.
Best regards,
Mark V.
Hi,
There should be another table which maps the products to managers.
@Ashish_Mathur
Hi Ashish,
I have created a pbix with date table to connect the tables together.
https://www.dropbox.com/s/mh1146kycg2hq1k/Product%20Count%20Based%20On%20Shift.pbix?dl=0
Please let me know if this helps. Thanks.
Best regards,
Mark V.
1. Create column as Date part only
Date Finished DT = DATEVALUE('Production Schedule'[Date Finished])
mark the format as "mm/dd/yyyy" to the new column
similar format to Manager schedule - start date column.
2. Adjust the model and see if it works for your needs
See if this helps
@sevenhills
Hi,
Thank you for your proposed solution but I need the count of products finished that is within the manager's respective schedule.
I have tried your proposed solution. It does not differentiate how many products where finished by each manager during the day. It shows the same count of products for Manager A and B for that day.
My bad, I misunderstood.
I kept the same changes mentioned above and added a new measure and created new visual. Please see if this helps
a) Created a measure part of manager schedule
Prod Count =
var _d1 = Minx('Manager Schedule', 'Manager Schedule'[Start Time])
var _d2 = Minx('Manager Schedule', 'Manager Schedule'[Clock Out])
var _Prod_count = CALCULATE( COUNTROWS('Production Schedule'), FILTER('Production Schedule', 'Production Schedule'[Date Finished] >= _d1 && 'Production Schedule'[Date Finished] <= _d2))
return _Prod_count
@sevenhills
Hi,
Thank you for the revision. It seems the total per day for 9/27/2021 is not the same as the calculated measure.
Product Count
Calculated Measure
I think what I provided is correct.
Let me say why, In your product count grid, it is not counting empty (string) Produc ID. All these are part of the manager 2 schedule when you verify the data and are on 9/27. You can check the data tab. You have empty Produc ID in your data.
In your Product Count visualization table, you are using default summarization by power bi and it takes only non empty Produc ID.
You can verify using to match the counts:
@sevenhills
Sorry about that. Thank you for letting me know that there are empty fields on the data.
Thank you for helping me. It's working perfectly.
Best regards,
Mark V.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
82 | |
66 | |
58 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |