Forum Discussion

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

Filter Calculation to only look at specific workgroup

I have some dax that finds the minimum date/time value if a specific workgroup is present. It works great if only one work group is within a task but when multiple workgroups that I'm looking at are within the same task it can get confused. I think the way around this is to make sure the overlaying workgroup matches the "sub-workgroup". 

 

So basically looking at the minimum time for rows where BI_WORKGRP2 = BI_WORKGRP3. 

 

In the picture below on the right you can see the overlayed workgroup (darkest row time 5:59 AM) is "SUB" within that task I want to get the minimum value where the workgroups equal. In this case "SUB". For reference WORKGRP2 is for the section on the right and WORKGRP3 is the section on the left. 

 

Again this code works if only one of the needed workgroups is present but I'd like it to be better by only looking at matched workgroups.

 

02.Min.Task Time by SO# = CALCULATE(min(Workflow[BI_EVENT_DT_TM]),allexcept(Workflow,Workflow[BI_SO_NBR],Workflow[BI_WRKFLW_TASK_SEQ_NBR],Workflow[BI_WORK_EVENT_CD]),or(Workflow[BI_WORKGRP3]="CONST",or(Workflow[BI_WORKGRP3] = "SUB", or(Workflow[BI_WORKGRP3] = "CONFOLLOW", or(Workflow[BI_WORKGRP3]= "FBRAMISCHD", Workflow[BI_WORKGRP3] = "FBRSUBCON")))))
Need Help
Tutorial Requests
  • v-yuta-msft's avatar
    v-yuta-msft
    7 years ago

    GunnerJ ,

     

    If the "Group" and "SubGroup" you mentioned are two different tables you can modify your measures using dax below:

    Min.Task Time by SO# =
CALCULATE (
    MIN ( Workflow[BI_EVENT_DT_TM] ),
    FILTER (
        ALLEXCEPT (
            Workflow,
            Workflow[BI_SO_NBR],
            Workflow[BI_WRKFLW_TASK_SEQ_NBR],
            Workflow[BI_WORK_EVENT_CD]
        ),
        Workflow[BI_WORKGRP3] = RELATED ( AnotherTable[BI_WORKGRP2] )
    )
)

    If you the [BI_WORKGRP2 ] and [BI_WORKGRP3] are two columns in the same table 'Workflow', you can modify your measure as below:

    Min.Task Time by SO# =
CALCULATE (
    MIN ( Workflow[BI_EVENT_DT_TM] ),
    FILTER (
        ALLEXCEPT (
            Workflow,
            Workflow[BI_SO_NBR],
            Workflow[BI_WRKFLW_TASK_SEQ_NBR],
            Workflow[BI_WORK_EVENT_CD]
        ),
        Workflow[BI_WORKGRP3] IN AnotherTable[BI_WORKGRP2]
    )
)

    Community Support Team _ Jimmy Tao

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

1 Reply

  • v-yuta-msft's avatar
    v-yuta-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    GunnerJ ,

     

    If the "Group" and "SubGroup" you mentioned are two different tables you can modify your measures using dax below:

    Min.Task Time by SO# =
CALCULATE (
    MIN ( Workflow[BI_EVENT_DT_TM] ),
    FILTER (
        ALLEXCEPT (
            Workflow,
            Workflow[BI_SO_NBR],
            Workflow[BI_WRKFLW_TASK_SEQ_NBR],
            Workflow[BI_WORK_EVENT_CD]
        ),
        Workflow[BI_WORKGRP3] = RELATED ( AnotherTable[BI_WORKGRP2] )
    )
)

    If you the [BI_WORKGRP2 ] and [BI_WORKGRP3] are two columns in the same table 'Workflow', you can modify your measure as below:

    Min.Task Time by SO# =
CALCULATE (
    MIN ( Workflow[BI_EVENT_DT_TM] ),
    FILTER (
        ALLEXCEPT (
            Workflow,
            Workflow[BI_SO_NBR],
            Workflow[BI_WRKFLW_TASK_SEQ_NBR],
            Workflow[BI_WORK_EVENT_CD]
        ),
        Workflow[BI_WORKGRP3] IN AnotherTable[BI_WORKGRP2]
    )
)

    Community Support Team _ Jimmy Tao

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