Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
NithinBN
Helper II
Helper II

Filtering based on related table

Hello All, 

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

This is how it looks like. 

Relation

NithinBN_0-1697372886040.png

Employee

NithinBN_1-1697372917522.png

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

 

NithinBN_2-1697372980906.png

 

I added a chart to check Developes hours. 

NithinBN_3-1697373084335.png

But i want to filter employee based on the manager. 

How to achive this ? 

Thank you !

 

1 ACCEPTED SOLUTION
v-xinruzhu-msft
Community Support
Community Support

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

vxinruzhumsft_0-1697523689633.png

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

 

View solution in original post

2 REPLIES 2
v-xinruzhu-msft
Community Support
Community Support

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

vxinruzhumsft_0-1697523689633.png

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
Super User
Super User

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

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.