Hi Alexandre,

In this case, say you have your Employees Dimension, with email as a property

Then your Emails Dimension with ‘Employee Name’ as a Dimension Property

You can use the ITEM() function to bring the names of the employees back, using their emails

There’s a better write up of the ITEM() function here: ITEM function | Community (gopigment.com)

I hope this helps, but let me know if there’s any more questions!

Hello,

That is not what I want because I don’t have Email dimension and I don’t want to have one.

There is no possibility to do it without Email dimension ?

Hi Alexandre,

You just need to refer to the dimension property you want to lookup, it dosen’t have to be a dedicated dimension

ITEM(Dimension.PropertyYouWantToLookup , ReturnDimension.PropertyYouWantToReturn)

I tried it but I have this error message : Error: Dimension error: formula Dimensions and current Block Dimensions are not compatible. Can't implicitly remove Employees Dimension(s) on non numerical Metric.

Hi Alexandre,

If you raise a ticket (Submit a request – Pigment) with a link to the Block, I’ll take a look.

Just updating following the ticket, the solution was;

ITEM(

IF(Employee.Work_Email = 'Other_List'.Persons_Email, Employees.'Employee #' )

[REMOVE FIRSTNONBLANK : Employee] , Employee)

This works by finding the matching email address, returning their employee ID and then using the ITEM() function to find the relevant Dimension to return, rather than a text value.

We’ve used the following functions here;

ITEM function | Community (gopigment.com)

IF function | Community (gopigment.com)

REMOVE modifier | Community (gopigment.com)

This guide goes through FIRSTNONBLANK

Get the first / last time a value appears in a metric: formula building guide | Community (gopigment.com)

Hi community,

It is a simple thing. I’m struggling with a lookup type formula.

(value to lookup, where to lookup, value to return)

I’m trying to populate a column ‘Report’ (property type dimension) in a Transaction List

In the same Transaction List I have the lookup field (property type dimension)

In ‘Account and Entity’ Dimension list, I have The lookup fieId ‘Concat’ (unique field, property text).

In the same dimension list I have the return field ‘Report’ (property dimension).

‘Report’ is a dimension property and it is linked to its own dimension list ‘Report 2’.

I iterated several times with item formula based on the above advice without success.

What is the logic to replicate a lookup as described above?

I think you could just chain properties? Like Transaction.'Account and Entity'.Report

It was as simple as that! 

Thanks a lot François :)