If statement with criteria from table and output from another

[boullioh]

I am new to Power BI, and development is not really "what I do" typically, so I hope I can describe my issue here well enough to get some help! I am trying to create a column that will display employee salary unless the employee is a manager, in which case I just want it to display $0.  The conditional criteria is coming from one table (EmployeeTable[title]) and the output is either 0 (if title = manager) or the value in another table (SalaryTable[salary]).  I am using an if statement and this is what I have:

SalaryDisplayed = If(EmployeeTable[title] = "Manager", 0, SalaryTable[salary])

I am getting an error "A single value for column 'title' in table 'EmployeeTable' cannot be determined.  This can happen when a measure formulat refers to a column that contains many values..."

I think maybe it is does not know how to reference this for the particular employee, but not sure.  Employee Name is also a column in my table.  

Any help would be very much appreciated! (Apologies if I entered this twice by mistake.  I tried to submit this earlier and it appeared to lose my entry.)

[Jihwan_Kim]

Hi, @boullioh 

Please check the below picture and the sample pbix file's link down below, whether it is what you are looking for.

Salary Disply CC =

VAR currentemployee = Salary[Employee]

RETURN

IF (

MAXX (

FILTER ( Employee, Employee[Employee] = currentemployee ),

Employee[Title]

) = "Manager",

0,

Salary[Salary]

)

https://www.dropbox.com/s/efvr9xbbcnpur4h/boullioh.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[boullioh]

Hi Jihwan, and thank you for the prompt and detailed solution.  I tried it and am having one issue still.  Where you define var currentemployee = Salary[employee], I do not have a field for employee in that salary table.  The salary table has a StaffKey and that is what the relationship between the Salary and Employee tables are based on.  However, staffkey is a numerical field - I am not sure how to define the var currentemployee without that option??

Sorry - still a novice, but learning!  Thank you again!

[Jihwan_Kim]

Hi, @boullioh 

Please share your sample pbix file's link here, then I can try to look into it to come up with a more accurate measure.

Thank you.

[AllisonKennedy]

@boullioh  If you are creating this as a column, it needs to be in the EmployeeTable:

SalaryDisplayed = If(EmployeeTable[title] = "Manager", 0, RELATED(SalaryTable[salary]))

However, I need to see the relationships from your Model view to verify that that will work for you. If you're having issues still, please share your model view diagram and also mock up of what you need as end result, as we may be able to use a Measure instead of column too.

[boullioh]

Hi Allison, thank you for your prompt response.  I was initially trying to create this using a measure, but tried also as a column after reviewing your suggestions.  The good news is when I create the column, at least now I can get it to work with numbers as outputs (0 if manager, 1 if not) so I know the first half is working.  Now I just need to get it to display the salary instead of 1.  The error I am now getting is "The column 'SalaryTable[salary]' either doesn't exist or doesn't have a relationship to any table available in the current context."

I do have a relationship between the 2 tables.  I am using sample table names so I will just describe here:

From Table (Column):                     To Table (Column):

SalaryTable[StaffKey]                       EmployeeTable[StaffKey]

Cardinality: Many to one, Cross filter direction:Single

Also, I think part of the issue is that I did not have a column for Salary - it is a field.  I created a column but still not working.  Sorry - I think part of my issue describing my problem is that I don't have the proper vocab in some cases.