Filtering based on related table

[NithinBN]

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 !

[v-xinruzhu-msft · Accepted solution]

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

[jdbuchanan71]

@NithinBN 

Take a look at this article the describes how to create an employee hierarchy.  

https://medium.com/@gulcekincrk/have-you-ever-wondered-how-to-create-an-organizational-hierarchy-in-...

I think you will need to reorganize your Relation table a bit so you have each employee once and who their manger is.  Then the article above describes how to build the chain of employees from the bottom to the top.