Creating a Calculated Dimension With Nested If Function, Based on Values on a Linked Table

[bdickinson3]

I have been attempting to create a calculated dimension that calculates the values in a column to a table linked through an ID.

=if(Count([Employee.Level] = '01') >= 1 AND Count([Employee.Level] = '02') < 1 AND Count([Employee.Level]] = '03') < 1 AND Count([Employee.Level] = '04') < 1 AND Count[Employee.Level] = '05') < 1, 'New Hire', '1 Year+')

The expression editor says that the script is okay, but whenever I close out of it it claims it as an invalid dimension.

[hic]

The expression is OK as measure, but not as dimension. You cannot have aggregation functions in a dimension, unless you use Aggr().

So, the question is: Per WHAT do you want to calculate all these counts? Per department? Per month? Per Employee category? You need an internal iterator - a dimension in the Aggr(). If it is per Department, you should use:

=Aggr(

if( Count([Employee.Level] = '01') >= 1 AND 

Count([Employee.Level] = '02') < 1 AND 

Count([Employee.Level]] = '03') < 1 AND 

Count([Employee.Level] = '04') < 1 AND 

Count[Employee.Level] = '05') < 1, 

'New Hire', 

'1 Year+'

),

Department

)

[bdickinson3]

Thank you. I'm afraid I have another problem though.

I wanted to use this expression as a filter pane with both options, but it's only showing "1 Year +" as an option. I wanted to be able to filter Incident IDs based on the highest-level employee involved in the incident, where Employees are linked together by similar Incident IDs (I realize I have to also include Incident_ID into the expression).

I appreciate any advice with this.