IF Function - 2 Conditions,

[Spencer]

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:

1. 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:

[Anonymous]

Also see a free training video here:

https://www.vrlearntime.com/product/power-bi-if-function-with-two-or-more-conditions/

[KGrice · Accepted solution]

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")

[amysmith]

Hi!

We have added a video on how to use the if function with multiple conditions on 

http://power-bi-training1.teachable.com/p/power-bi-free-tutorial-videos/

[Spencer]

Thats worked perfectly. Huge thanks @KGrice.

[KGrice]

You're welcome @Spencer!