Find Manager in the filtered list and put 1 against his ID

[LP280388]

Hi team,

have data in the below format. 

employee	Name	Manager
101	COE
102	Deputy CEO	101
103	Sr Manager1	102
104	Sr Manager2	102
105	Dev 3	103
106	Dev 4	103
107	Dev 5	103
108	Dev 6	106
109	Dev 7	104
110	Dev 8	104

When the RLS is applied for Sr. Manager1 below is the output seen to see himself and his direct reportees.  

I have the below dax to put 1 against the employee when they are present in the Manager columns.  

corpismgr? = IF(CALCULATE(COUNT('table'[employee]),'table'[employee] in VALUES('table'[Manager]))>=1,1,0)

Currently, its putting in 1 for employee 106 as well since he is a manager for 108. 

employee	Name	Manager	output expected
103	Sr Manager1	102	1
105	Dev 3	103	0
106	Dev 4	103	1
107	Dev 5	103	0

I would need the output as below. the dax should be looking for a manager within the filtered list.  can anyone help me how to modify the dax above to restrict to filtered list only?

Below is the output expected when Sr. Manager1 logs in.

employee	Name	Manager	output expected
103	Sr Manager1	102	1
105	Dev 3	103	0
106	Dev 4	103	0
107	Dev 5	103	0

[danextian · Accepted solution]

hi @LP280388 

This might be what you're looking for

Employee is Manager = 
CALCULATE (
    COUNTROWS ( 'Table' ),
    'Table'[employee] IN ALL ( 'Table'[Manager] )
)

Note: You cannot make the other employee appear 0 as Sr Manager1 is the only row that is visible based on the current filter.

[Anonymous · Accepted solution]

Hi,

Thanks for the solution danextian  and bhanu_gautam  offered, and i want to offer some more infotmation for user to refer to.

hello @LP280388 , you can refer to the following solution.

Create a new table.

Employee = SUMMARIZE('Table',[employee],[Name])

Then create a measure

MEASURE =
VAR a =
    CALCULATETABLE (
        VALUES ( 'Table'[employee] ),
        FILTER ( 'Table', [Manager] IN VALUES ( 'Employee'[employee] ) )
    )
RETURN
    IF (
        SELECTEDVALUE ( 'Table'[employee] ) IN VALUES ( 'Employee'[employee] ),
        1,
        IF ( COUNTROWS ( a ) > 0, 0 )
    )

Then create a slicer and put the name field of the employee table to the slicer. and create a table visual, put the measure and the field of the data table to it.

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.

[danextian · Accepted solution]

hi @LP280388 

This might be what you're looking for

Employee is Manager = 
CALCULATE (
    COUNTROWS ( 'Table' ),
    'Table'[employee] IN ALL ( 'Table'[Manager] )
)

Note: You cannot make the other employee appear 0 as Sr Manager1 is the only row that is visible based on the current filter.

[bhanu_gautam]

@LP280388 , Try using this 

corpismgr? = IF(
CALCULATE(
COUNT('table'[employee]),
FILTER(
ALL('table'),
'table'[employee] = EARLIER('table'[Manager])
)
) >= 1,
1,
0
)

[LP280388]

@bhanu_gautam , This is giving 1 for all the records filtered like below 

employee	Name	Manager	output expected
103	Sr Manager1	102	1
105	Dev 3	103	1
106	Dev 4	103	1
107	Dev 5	103	1

It should give 1 against 103 employee ID only