cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
GunnerJ Member
Member

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. 

comparing Workgroups.PNG

 

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")))))
1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Filter Calculation to only look at specific workgroup

@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 1
Community Support Team
Community Support Team

Re: Filter Calculation to only look at specific workgroup

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

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 426 members 5,025 guests
Please welcome our newest community members: