Select the active record

[xarapre7]

Hello!

I have data that shows employee_key, employee_name, employee_id, emp_status and country.
An employee can have multiple emp_status.  My expression in the text box is below:

=Num(Sum({1} Aggr(Sum({$<EMPLOYEE_NAME,COUNTRY=p(COUNTRY)>} CALLS)/
Count({$<EMPLOYEE_NAME=,COUNTRY=p(COUNTRY)>} Aggr(If(Sum({$<EMPLOYEE_NAME=,COUNTRY=p(COUNTRY)>} CALLS) > 0, 1), DATE, EMPLOYEE_NAME=)), DATE)),'#,##0')

Right now, it's picking whatever is the 1st record for that employee which doesn't tie up to any country so the output it gives is 0.

EMP_KEY, EMP_NAME, EMP_ID, EMP_STATUS, COUNTRY
123             JOE               12001      WITHDRAWN     -
123             JOE               12001      WITHDRAWN     -
234             JOE               12010      ACTIVE              US

What's the right expression to only display country from the active record?

Thanks in advance for your help!

[Miguel_Angel_Baeyens]

Using the Only() function will return a value, either it be a literal string or a number. If you need to further format it, you can use functions like Text() or Date#(), or etc. If Only() returns more than one value, then you need to use a Max() or Rank(), or FirstSortedValue() or Aggr() function to make sure you are returning one single value. But again, regardless the type of value, you can represent text in a text object using those functions.

Some examples:

Find out min and max Dates and show a value only from this row

What's the aggr dimension value for the highest value of expression

Count items with max date

*** Equivalent to VLookup in Excel (Returning a String Value) by Using Expression *** (Simple Questi...

and eventually Displaying the Top Salesperson in a Textbox

[xarapre7]

Still gives me 0.

[sunny_talwar]

Without the red part you are getting what you want? Just by adding EMP_STATUS={'ACTIVE'} the output becomes 0?

[xarapre7]

Is there a way to do the filter in the DataModel?  If so, can you please let me know?

Thank you all for your kind assistance.

[sunny_talwar]

You are only looking to load data where EMP_STATUS = ACTIVE? If that's true, then load with where statement

LOAD .....

FROM ....

Where EMP_STATUS = 'ACTIVE';

[xarapre7]

I tired it with and without the red part, the result is 0

[sunny_talwar]

So your original expression isn't working then?

[xarapre7]

I only need to pull the Country data for record that is "ACTIVE" if there are multiple records for that employee.

The above expression will filter for just the "ACTIVE" for all the records of employees.

[sunny_talwar]

Yes, it will pull all those rows where EMP_STATUS = ACTIVE

[xarapre7]

Actually, I need to pull all the records of the employees may it be withdrawn or active at the DataModel level.  But in the UI, that's where I'm having the challenge of filtering the record based on the employee's active record given the above expression.  The expression is pulling the withdrawn record.  Even with the set analysis of EMP_STATUS={'ACTIVE'}, it's still not giving the desired result.