Forum Discussion
IF Function - 2 Conditions,
Hi I’m trying to add multiple conditions to an IF function within a calculated column in order to register an employees status as either active or inactive.
The way I’m determining Status is via two conditions. The employee should show up Active if:
- Their reason_code cell is blank. Which is located in the Employee Master field
And
2. They haven’t got an inactive_date. Which is located in the InactiveEmployees field
This is the formula I’ve tried to use:
Status = IF('EmployeeMaster'[reason_code]=BLANK() && 'InactiveEmployees'[inactive_date]=BLANK(),"Active","Inactive")
But I receive the error “A single value for column ‘inactive_date’ in table ‘InactiveTableTest’ cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."
The Employee Master and InactiveTableTest are two different fields. Do I need to be using the REALTE function to deal with this?
Any help greatly appreciated.
Screenshots:
Yes, you're on the right track with related. Assuming your InactiveEmployees table doesn't have duplicates, you'll want to create a relationship between the EmployeeMaster and InactiveEmployees tables on the employee_no field.
From the Home tab, go to Manage Relationships. If it didn't autodetect it and create it for you already, click New, select EmployeeMaster in the top and InactiveEmployees in the bottom table drop-downs, select the employee_no field in both tables, and set the Cardinality to Many to One.
With the relationship in place, you can adjust your formula:
Status = IF('EmployeeMaster'[reason_code]=BLANK() && RELATED('InactiveEmployees'[inactive_date])=BLANK(),"Active","Inactive")
5 Replies
Yes, you're on the right track with related. Assuming your InactiveEmployees table doesn't have duplicates, you'll want to create a relationship between the EmployeeMaster and InactiveEmployees tables on the employee_no field.
From the Home tab, go to Manage Relationships. If it didn't autodetect it and create it for you already, click New, select EmployeeMaster in the top and InactiveEmployees in the bottom table drop-downs, select the employee_no field in both tables, and set the Cardinality to Many to One.
With the relationship in place, you can adjust your formula:
Status = IF('EmployeeMaster'[reason_code]=BLANK() && RELATED('InactiveEmployees'[inactive_date])=BLANK(),"Active","Inactive")Also see a free training video here:
https://www.vrlearntime.com/product/power-bi-if-function-with-two-or-more-conditions/
