Forum Discussion

GunnerJ's avatar
GunnerJ
Icon for Post Patron rankPost Patron
7 years ago

AddColumns where a condition is met

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". I...
Need Help
Tutorial Requests
dax's avatar
dax
Icon for Community Support rankCommunity Support
7 years ago

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's avatar
GunnerJ
Icon for Post Patron rankPost Patron
7 years ago

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