Vlook up or similar function

[Lobo77]

Hi.

Is there anyway of doing a VLOOKUP equivalent in a  table/chart  ?

I have gone through other posts , but have not found an answer that suits my needs in this instance.

Note – I do not have access to the load script for this application , and need to be able to do this in a chart/table.

Below is the hierarchy of an order.

Order_ID is  built up of  components , the components have unique service id  and  individual tasks.

Currently , when I select Task “A” , I can only see the Service id for the Fibre Broad band.

What I would like to do,  regardless of the task I select , I wish to have a calculated dimension that will still display the Voip Service id  for the Order_id.

For example , if I selected Task B , I would be able see the service ID of the Voip service ( like the fictitious  Calculated Service id  , highlighted in red ).

Thanks for any assistance in advance.

ORDER_ID	Component Type	Task	Service ID	Calculated Service ID
182112560	Fibre Broad Band	A	My_username	0734111086
182112560	Fibre Broad Band	B	My_username	0734111086
182112560	Voip	C	0734111086	0734111086
182112560	Voip	D	0734111086	0734111086

[tresesco]

@Lobo77  What is the change would you expect in this table when no task is selected vs when task 'B' is selected?

[Lobo77]

Hi @tresesco . If no task is selected , the voip service Id would still be displayed if possible. Edit: But open to solutions. Thanks

[tresesco]

@Lobo77 ,

Sorry, not clear to me yet. I am assuming the table that you shared above is the output when nothing is selected, how would the table output be when 'B' is selected? Could explain the output tables in different selection criteria?

[Lobo77]

@tresesco  apologies.

This is in a straight table.

Currently , if I was to select ( by filtering)   the Task  to  "B" of the ORDER_ID ,  the table then  only shows me the  Service id "My_Username" for the Order_ID.   I can no longer see the "Voip " Service_id , like below.

ORDER_ID	Component Type	Task	Service ID
182112560	Fibre Broad Band	B	My_username

What I would like to do by adding a calculated dimension , is regardless of the task selected  , I would always be able to to see the Service_id that belongs to the "Voip" Component Type of that Order_ID , like below :

The  new Calculated service_id is the dimension I would like to add if possible.

ORDER_ID	Component Type	Task	Service ID	Calculated Service ID
182112560	Fibre Broad Band	B	My_username	0734111086

Hopefully this makes sense , and thanks again for having a look.

[tresesco]

@Lobo77 Try like this :

Only(total {1<[Component Type]={'Voip'}>} [Service ID])

[swuehl · Accepted solution]

Or if you need this per ORDER_ID:

=Only(TOTAL<ORDER_ID> {1<[Component Type]={'Voip'}>} [Service ID])

---

Maybe you also need to modify your set expression to be able to filter your table accordingly.

And if there could be multiple Service ID task based:

=CONCAT(DISTINCT TOTAL<ORDER_ID> {1<[Component Type]={'Voip'}>} [Service ID], '; ')

[Lobo77]

Thanks @swuehl  and @tresesco 

The one that suits is =CONCAT(DISTINCT TOTAL<ORDER_ID> {1<[Component Type]={'Voip'}>} [Service ID], '; ') , as there is somtimes 2 Voip per order.

I will , like you have mentioned modify to be able to filter , as currently can not filter the table ( what ever I filter , the table shows all orders and associated tasks ).

Thanks so muck for both your help !