Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I'm calculating the length between the largest and smallest date/times of my data set and the calculation returns the value for each row of the column. Is there a way to only return the value for one row and leave the others blank so that I can calculate averages accurately? I was thinking the code could revolve around placing the time next to the largest or smallest date.
For example for this service order number I have sequence #1 taking 17 seconds. If I did an average on this Task_CD three values of 17 seconds would be applied when it should just be one.
Solved! Go to Solution.
That's because the averages are calculating for the individual SO_NBR. When you put it in a pie chart with just task_cd the meausre can't resolve what you mean. It might be easier and more stable to use DAX to make a summary table with the times calculated that you can the do averages and sums over:
SummaryTable = ADDCOLUMNS( SUMMARIZE( Workflow, Workflow[BI_SO_NBR], Workflow[BI_TASK_CD], Workflow[BI_WRKFLW_TASK_SEQ_NBR], "Min Time",MIN (Workflow[BI_EVENT_DT_TM]), "Max Time",MAX (Workflow[BI_EVENT_DT_TM]) ), "Time Sec", DATEDIFF([Min Time],[Max Time],SECOND), "Time HR", DATEDIFF([Min Time],[Max Time],HOUR) )
Hello @GunnerJ ,
If you make [Task Time] into a measure instead of a calculated column.
Task Time = VAR SEQ_NBR = SELECTEDVALUE ( Workflow[BI_WRKFLW_TASK_SEQ_NBR] ) VAR SO_NBR = SELECTEDVALUE ( Workflow[BI_SO_NBR] ) VAR TASK_CD = SELECTEDVALUE ( Workflow[BI_TASK_CD] ) VAR RESULT = CALCULATE ( DATEDIFF ( MIN ( Workflow[BI_EVENT_DT_TM] ), MAX ( Workflow[BI_EVENT_DT_TM] ), SECOND ), FILTER ( Workflow, Workflow[BI_WRKFLW_TASK_SEQ_NBR] = SEQ_NBR && Workflow[BI_SO_NBR] = SO_NBR && Workflow[BI_TASK_CD] = TASK_CD ) ) RETURN RESULT
You can use that in an AVERAGEX measure that should get what you are looking for.
Avg Time = AVERAGEX( ALL ( Workflow[BI_TASK_CD], Workflow[BI_WRKFLW_TASK_SEQ_NBR]), [Task Time] )
I added a second SO_NBR to test the measures. Min DT and Max DT are also measures for testing.
@jdbuchanan71 I'm unsure why but the measure only returns zeros for me. I'll play around with it but is there anything you could think of that would cause that?
Try changing the Task Time measure to this. It makes the AVG time measure much more stable.
Task Time = IF ( ISFILTERED ( Workflow[BI_SO_NBR] ), CALCULATE ( DATEDIFF ( MIN ( Workflow[BI_EVENT_DT_TM] ), MAX ( Workflow[BI_EVENT_DT_TM] ), SECOND ), ALL(Workflow[BI_EVENT_DT_TM]) ) )
@jdbuchanan71 Thank you very much for your responses. I feel as though the final solution is close. To summize everything The last Task Time measure shows the value in a table but when I put it in a pie chart the chart goes blank. The only other value there is TASK_CD which is in the legend.
I used the previous code and the divide the average measure by 3600 to get the average hour time.
That's because the averages are calculating for the individual SO_NBR. When you put it in a pie chart with just task_cd the meausre can't resolve what you mean. It might be easier and more stable to use DAX to make a summary table with the times calculated that you can the do averages and sums over:
SummaryTable = ADDCOLUMNS( SUMMARIZE( Workflow, Workflow[BI_SO_NBR], Workflow[BI_TASK_CD], Workflow[BI_WRKFLW_TASK_SEQ_NBR], "Min Time",MIN (Workflow[BI_EVENT_DT_TM]), "Max Time",MAX (Workflow[BI_EVENT_DT_TM]) ), "Time Sec", DATEDIFF([Min Time],[Max Time],SECOND), "Time HR", DATEDIFF([Min Time],[Max Time],HOUR) )
@jdbuchanan71 is there a way to force the min date to be the the smallest date with
@GunnerJ ,
I'm not sure what you are looking for exactly. Please upload a sample .pbix for everyone to work on and an example of what you would like the final output to be (mocup in excel is fine).
You are also posting on a thread that is marked as closed now so you may want to open a new thread with your sample, example and question so others will see it.
@jdbuchanan71 Thank you very much for your help! You've allowed me to complete my request as I had originally asked. I do have some further modifications but they get a little more specific. Would it be ok to message you if I should need some further assistance?
Hello @GunnerJ ,
You are better off posting on the forum that way, if I am not available, other will see the message as well and be able to help. There are also users on here with far more knowledge than I have that will have better solutions. Finally, there are Microsoft people that watch the boards and answer questions as well.
If you can, always include a sample .pbix file with your questions (load it to drop box or one drive) so the people trying to help can see the measures and relationships you are working with.
User | Count |
---|---|
140 | |
113 | |
104 | |
76 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |