Forum Discussion

Gopinath_iyer's avatar
Gopinath_iyer
Helper II
5 years ago
Solved

filter out upper level data

Dear Friends,

 

I am new to power bi and struct with a case. Hope to get some inputs here.

i will try to explain...let say we have employees data of an organization.

 

i have created simple table with employee name, age, salary and i want give a filter for employee selection to the user.

basis the user selection i want to filter out the upper level employees. e.g if C reports to B and B reports to A.

 

if user select A in filter we will see 3 rows A,B and C

if user selects B in filter we should see 2 rows B and C

if user selects C in filter we should only see 1 row C

 

thanks in advance 

Gopi

Need Help
  • v-kelly-msft's avatar
    v-kelly-msft
    5 years ago

    Hi  Gopinath_iyer ,

     

    Create a slicer table as below:

    Table 2 = VALUES('Table'[employee name ])

    Then create 2 measures as below:

    _employee name = SWITCH(SELECTEDVALUE('Table 2'[employee name ]),
"A",CALCULATE(MAX('Table'[employee name ]),'Table'[employee name ] in FILTERS('Table'[employee name ])),
"B",CALCULATE(MAX('Table'[employee name ]),'Table'[employee name ] in FILTERS('Table'[employee name ])&&'Table'[employee name ]<>"A"),
"C",CALCULATE(MAX('Table'[employee name ]),FILTER(ALL('Table'),'Table'[employee name ]="C")))
    _age = 
var age=CALCULATETABLE(VALUES('Table'[age]),FILTER(ALL('Table'),'Table'[employee name ]=[_employee name]))
Return
IF(MAX('Table'[age]) in age,MAX('Table'[age]),BLANK())

    And you will see:

     

    For the related .pbix file,pls see attached.

     

    Best Regards,
    Kelly

    Did I answer your question? Mark my post as a solution!

     

2 Replies

  • v-kelly-msft's avatar
    v-kelly-msft
    Community Support
    5 years ago

    Hi  Gopinath_iyer ,

     

    Create a slicer table as below:

    Table 2 = VALUES('Table'[employee name ])

    Then create 2 measures as below:

    _employee name = SWITCH(SELECTEDVALUE('Table 2'[employee name ]),
"A",CALCULATE(MAX('Table'[employee name ]),'Table'[employee name ] in FILTERS('Table'[employee name ])),
"B",CALCULATE(MAX('Table'[employee name ]),'Table'[employee name ] in FILTERS('Table'[employee name ])&&'Table'[employee name ]<>"A"),
"C",CALCULATE(MAX('Table'[employee name ]),FILTER(ALL('Table'),'Table'[employee name ]="C")))
    _age = 
var age=CALCULATETABLE(VALUES('Table'[age]),FILTER(ALL('Table'),'Table'[employee name ]=[_employee name]))
Return
IF(MAX('Table'[age]) in age,MAX('Table'[age]),BLANK())

    And you will see:

     

    For the related .pbix file,pls see attached.

     

    Best Regards,
    Kelly

    Did I answer your question? Mark my post as a solution!

     

  • Gopinath_iyer's avatar
    Gopinath_iyer
    Helper II
    5 years ago

    Hi 

     

    Sorry for replying so late.

     

    Thanks for the solution...but the real data has many names, in this solution emp names are hard coded.

     

    Can we make it dynamic?

     

    regards,

    Gopi