filter out upper level data

[Gopinath_iyer]

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

[Gopinath_iyer]

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

[v-kelly-msft · Accepted solution]

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!