AddColumns where a condition is met

[GunnerJ]

I have a summary table that calculates the difference between a min and max date. I'd like to modify it to only show data where the "Min Date" is the earliest record with a work group = "Service". If no "service" work group is present then filter out the data. If it is present make the earliest BI_EVENT_DT_TM with service be the Min date and leave the max date as it is. 

 This data set would be excludedThis data set would be included and min date would be the row with Service as the workgroup.

SummaryTable =

ADDCOLUMNS(

SUMMARIZE(

Workflow,

Workflow[BI_SO_NBR],

Workflow[BI_TASK_CD],

Workflow[BI_WRKFLW_TASK_SEQ_NBR],

Workflow[BI_WORK_EVENT_CD],

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

)

[dax]

Hi GunnerJ

According to your description, it seems that you want to show only data with “service ” group, right? If so, think you could use below expression to create new table (replace Table1[type]="c"|| Table1[type]="a" with your condition)

Table =
ADDCOLUMNS (
    SUMMARIZE (
        FILTER ( Table1, Table1[type] = "c" || Table1[type] = "a" ),
        Table1[id],
        Table1[type],
        "mind", MIN ( Table1[date] ),
        "maxd", MAX ( Table1[date] )
    ),
    "datediff", DATEDIFF ( [mind], [maxd], DAY )
)

Best Regards,

Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[GunnerJ]

@dax specifically I'm wanting just the "Min Time" date to filter down to the work group "Service". Sequences often end with tasks that don't have a specific workgroup so I'm just trying to find the different between the first task of workgroup = Service and the max date.

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)

)