Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I am trying to add up my cycle time and review time to give a column for lead time. the Cycle time is summing based on the count an Analysis (Tests) which is correct. however, the review time is doing the same, but I only need it to sum the review time for that row once, not for each count of analysis. Any help is greatly appreciated!
Solved! Go to Solution.
@Anonymous
pls see the attachment below. Hope it's helpful.
still have a question. why the date for expected output is 2020/1/15 and 2020/1/16?
Proud to be a Super User!
@Anonymous
what's the expected output? could you pls also provide some sample data?
Proud to be a Super User!
@ryan_mayu Yes here is another example that I hope makes more sense:
on day 31 the count of Analysis (tests) is 2. the cycle time for one test is 30 so the value should be 60 which it is. however, the Review time only occurs once regardless of the amount of tests completed (Count of Analysis). the review time is 60 minutes. so the expected value is 60 however I am getting 120.
Let me know if the pictures are not enough and I will do my best to post the data. sorry this is my first time on this community forum! thanks for the help!
Here is the data baswed on the above explanation of expected outcome.
Here is a photo of the data in the transform data window
@Anonymous
cycle time= count of analysis * 30
What's the DAX of reviewing time and lead time?
Proud to be a Super User!
In the Review Time column in the above picture, every row should have a value of 60. but the output I am getting is Review Time = Count Analysis*60. I only want it to show 60*1
@Anonymous
maybe you can try distinctcount(analysis)* average(testing leadtime)
Proud to be a Super User!
Yea still no luck. the only way to exaplain it is this way:
My lead time is the sum of cycle time and review time. if I have 10 samples to test my cycle time will be multiplied by 10. but I review the entire set of 10 samples one time.
so if I have 10 samples, my cycle time is 10, and my review time is 50 then the value of my lead time should be Lead Time = Cycle*#samples + Review Time = (10*10) + 50 = 150 minutes. my review time column contains a lot of different values for Review time based on the specific test in the Analysis column. so averaging, using Min, or Max will not work. any suggestions is appreciated
@Anonymous
sry, still not clear about this. Could you pls provide some sample data, not the screenshot and also provide the expected output based on your sample data?
Proud to be a Super User!
It will not let me post the tables for some reason. here is a link to the a file I created with Current Resutls, Expected Results, then Sample Data in that order.
Let me know if this works for you
https://drive.google.com/file/d/1OtKhdFohu8pSIIjRxbfgLm12OUjLppz9/view?usp=sharing
Sorry about that, it should have been april and august dates corressponding to the sample data. that was an error I made on accident when generating the file for you. This is exactly what I needed! thanks so much for the help and sorry for the confusion
you are welcome
Proud to be a Super User!
Sample Data:
Date | Testing Cycle Time | Testing Lead Time | Testing Review Time | Last Analysis | |
4/14/2020 | 20 | 60 | 45 | AZBR_ID_E_EP_N | |
4/14/2020 | 20 | 60 | 45 | AZBR_ID_E_EP_N | |
4/14/2020 | 20 | 60 | 45 | AZBR_ID_E_EP_N | |
4/14/2020 | 20 | 60 | 45 | AZBR_ID_E_EP_N | |
4/14/2020 | 20 | 60 | 45 | AZBR_ID_E_EP_N | |
8/4/2020 | 10 | 90 | 30 | AZBR_ID_A_EP_QC9522_N | |
8/4/2020 | 10 | 90 | 30 | AZBR_ID_A_EP_QC9522_N | |
8/4/2020 | 10 | 90 | 30 | AZBR_ID_A_EP_QC9522_N | |
8/4/2020 | 10 | 90 | 30 | AZBR_ID_A_EP_QC9522_N | |
8/4/2020 | 10 | 90 | 30 | AZBR_ID_A_EP_QC9522_N | |
8/4/2020 | 10 | 90 | 30 | AZBR_ID_A_EP_QC9522_N | |
8/4/2020 | 10 | 90 | 30 | AZBR_ID_A_EP_QC9522_N | |
8/4/2020 | 10 | 90 | 30 | AZBR_ID_A_EP_QC9522_N | |
8/4/2020 | 10 | 90 | 30 | AZBR_ID_A_EP_QC9522_N | |
8/4/2020 | 10 | 90 | 30 | AZBR_ID_A_EP_QC9522_N |
Current Results:
Year | Month | Day | Count of Product2 | Testing Cycle Time | Testing Lead Time | Testing Review Time | Total Time | Last Analysis |
2020 | April | 14 | 5 | 100 | 300 | 225 | 625 | AZBR_ID_E_EP_N |
2020 | August | 4 | 10 | 100 | 900 | 300 | 1300 | AZBR_ID_A_EP_QC9522_N |
Expected Result:
Year | Month | Day | Count of Product2 | Testing Cycle Time | Testing Lead Time | Testing Review Time | Total Time | Last Analysis |
2020 | January | 15 | 5 | 100 | 60 | 45 | 205 | AZBR_ID_E_EP_N |
2020 | January | 16 | 10 | 100 | 90 | 30 | 220 | AZBR_ID_A_EP_QC9522_N |
Total Time = SUM(Testing Cycle Time + Testing Lead Time + Testing Review Time)
Let me know if this helps!
Lead Time is SUM( Cycle Time + Lead Time)
Review Time should just be Distinct count of analysis*Lead time value in this case 60.
so it should just be Lead Time = 1*60 for this case. I have list of a bunch of different lead times.
@Anonymous
if i didn't misunderstand your explaination.
pls try
=distinctcount(analysis)* [testing cycle time]
Proud to be a Super User!
Here is another photo with the Sum of Testing Review Time