Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
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:
Solved! Go to 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")
Also see a free training video here:
https://www.vrlearntime.com/product/power-bi-if-function-with-two-or-more-conditions/
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")
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/
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |