Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
GunnerJ
Post Patron
Post Patron

Return one value and leave the rest blank

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. 

Task Seconds.PNG

 
This is the code used to calculate the task time. Any modifications to it that would leave all but one value blank for each sequence number or just another column that accomplishes the same thing would be appreciated.
 
Task Time =
CALCULATE (
DATEDIFF (
MIN ( Workflow[BI_EVENT_DT_TM] ),
MAX ( Workflow[BI_EVENT_DT_TM] ),
SECOND
),
FILTER (
ALL ( Workflow ),
Workflow[BI_WRKFLW_TASK_SEQ_NBR] = EARLIER ( Workflow[BI_WRKFLW_TASK_SEQ_NBR] )
&& Workflow[BI_SO_NBR] = EARLIER ( Workflow[BI_SO_NBR] )
&& Workflow[BI_TASK_CD] = EARLIER ( Workflow[BI_TASK_CD] )
&& Workflow[BI_WORK_EVENT_CD] = "COMP"
)
)
1 ACCEPTED 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)
)

avg_time_table.jpg

View solution in original post

9 REPLIES 9
jdbuchanan71
Super User
Super User

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] )

avg_time.jpg

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. 

Task Time =
VAR SEQ_NBR = SELECTEDVALUE ( Workflow1[BI_WRKFLW_TASK_SEQ_NBR] )
VAR SO_NBR = SELECTEDVALUE ( Workflow1[BI_SO_NBR] )
VAR TASK_CD = SELECTEDVALUE ( Workflow1[BI_TASK_CD] )
VAR RESULT =
CALCULATE (
DATEDIFF (
MIN ( Workflow1[BI_EVENT_DT_TM] ),
MAX ( Workflow1[BI_EVENT_DT_TM] ),
SECOND
),
FILTER (
Workflow1,
Workflow1[BI_WRKFLW_TASK_SEQ_NBR] = SEQ_NBR &&
Workflow1[BI_SO_NBR] = SO_NBR &&
Workflow1[BI_TASK_CD] = TASK_CD
)
)
RETURN RESULT
 
Avg Time =
AVERAGEX( ALL ( Workflow[BI_TASK_CD], Workflow[BI_WRKFLW_TASK_SEQ_NBR]), [Task Time2] )
 
 
*However, in my pie chart ALL of my task_cd types are showing and they all show the same value. When I filter down to a service order type sometimes the task_cds show (still all the same) and sometimes the chart goes blank. 
 
I have a test file that the formulas seem to be working with but I'm still testing what would make them behaive differently. Any suggestions?

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)
)

avg_time_table.jpg

@jdbuchanan71  is there a way to force the min date to be the the smallest date with 

Workflow[BI_WORKGRP3] = "SERVICE"? That way the end result would actually be how long the "service" portion to the end of the task took. I could then replicate this for other work groups. If the seq_num doesn't have any workgroup of "service" then we could filter it out. I've added the field of Workgorup but I'm unsure on how to filter just the Min Date section to look at it's value.
 
SummaryTable =
ADDCOLUMNS(
SUMMARIZE(
Workflow,
Workflow[BI_SO_NBR],
Workflow[BI_TASK_CD],
Workflow[BI_WRKFLW_TASK_SEQ_NBR],
Workflow[BI_WORK_EVENT_CD],
Workflow[BI_WORKGRP3],
"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)
)
 

@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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.