Forum Discussion

NithinBN's avatar
NithinBN
Helper II
2 years ago
Solved

Filtering based on related table

Hello All, 

I have 2 table, 1 Employee 2Relation between them.

This is how it looks like. 

Relation

Employee

1-N relation between Employee and Relation.  (EmployeeDetails:EmployeeID) with Relation (Employee-1).

 

 

I added a chart to check Developes hours. 

But i want to filter employee based on the manager. 

How to achive this ? 

Thank you !

 

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi NithinBN 

    You can create the following measure

    Measure =
VAR a =
    CALCULATETABLE (
        SUMMARIZE (
            FILTER (
                ALLSELECTED ( Relation ),
                CONTAINSSTRING ( [Employee-1], "M-" ) && CONTAINSSTRING ( [Employee-2], "D-" )
            ),
            [Employee-2]
        ),
        CROSSFILTER ( 'Relation'[Employee-1], Employee[EmployeeID], NONE )
    )
RETURN
    CALCULATE (
        SUM ( Employee[Hours] ),
        FILTER ( Employee, [EmployeeID] IN a ),
        CROSSFILTER ( 'Relation'[Employee-1], Employee[EmployeeID], NONE )
    )

    Output

    Best Regards!

    Yolo Zhu

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

     

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi NithinBN 

    You can create the following measure

    Measure =
VAR a =
    CALCULATETABLE (
        SUMMARIZE (
            FILTER (
                ALLSELECTED ( Relation ),
                CONTAINSSTRING ( [Employee-1], "M-" ) && CONTAINSSTRING ( [Employee-2], "D-" )
            ),
            [Employee-2]
        ),
        CROSSFILTER ( 'Relation'[Employee-1], Employee[EmployeeID], NONE )
    )
RETURN
    CALCULATE (
        SUM ( Employee[Hours] ),
        FILTER ( Employee, [EmployeeID] IN a ),
        CROSSFILTER ( 'Relation'[Employee-1], Employee[EmployeeID], NONE )
    )

    Output

    Best Regards!

    Yolo Zhu

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