is Manager? give 1 else 0

[Anonymous]

Hi Team,

I have a question looks pretty simple, but not able to have a crack at it.

I have two columns Emp ID and Manager ID. 

I need a measure to get 1 when the emp ID is also listed in ManagerID. 

I tried this measure :

is manager? = if(contains(table, table[mgrID],"xyz"),1,0)

But this gives "1" next to the employees where the xyz is a manager for them.

I need to get in the below format.

Current results with above formula.
Emp Id	Mgr ID	is Manager?
xyz	eef	0
abc1	xyz	1
abc2	xyz	1

I need :
EmpID	Mgr ID	is Manager?
xyz	eef	1
abc1	xyz	0
abc2	xyz	0

[Anonymous]

Hi @Anonymous ,

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file from your Onedrive for Business.

Best Regards,

Yuna

[Anonymous]

Hi @Anonymous ,

Based on your description, You can create a measure as follows.

Measure_for_IsManager = IF(MAX([EmpID]) in DISTINCT(ALL('1 else 0_Test'[Mgr ID])),1,0)

Result:

Hope that's what you were looking for.

Best Regards,

Yuna

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

@Anonymous  Hi Thanks for the response. This works perfectly fine when i have the filter in the same report.  However, the filter is actually not from the same report in my situation. I have a another report and provided link to this report from where the EmpID filter value flows. 

The selectedvalue() captures the empid.  But its not passing it to the if condition below

is manager = if(contains(table, table[managerid], selectedvalue(empid)),1,0)

How do I make it happen?

[Anonymous]

Hi @Anonymous ,

Maybe I'm not getting what actually you are trying to do. Do you mean that EmpId is on another table in the same report? If the data (or fields) are not in the same report, DAX functions or M queries are disable to be used. 

Could you please share some sample data and the expected result to have a clear understanding of your question? I can do some tests for you. Some screenshots are welcome.

Best Regards,

Yuna

[amitchandak]

@Anonymous , Try a new column

is Manager =

if(countx(filter(Table, [EmpID] =earlier([Mgr ID])),[Mgr ID])+0 >1,1,0)

[Anonymous]

@amitchandak   

I cant have a new column as there is a restriction from my company so i need a measure.  Would it be possible?

[amitchandak]

@Anonymous , Try this one, I tested this

Measure 2 = if(countx(filter(allselected('Table'), max([Emp ID]) =([Mgr ID])),[Mgr ID])+0 >1,1,0)

[Anonymous]

@amitchandak  Hi again, this formula says "too few arguments passed to the filter function

[amitchandak]

@Anonymous , Not tested yet, something like this

if(countx(filter(allselected(Table), [EmpID] =max([Mgr ID])),[Mgr ID])+0 >1,1,0)