Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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 What is the change would you expect in this table when no task is selected vs when task 'B' is selected?
Hi @tresesco . If no task is selected , the voip service Id would still be displayed if possible. Edit: But open to solutions. Thanks
@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?
@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.
@Lobo77 Try like this :
Only(total {1<[Component Type]={'Voip'}>} [Service ID])
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], '; ')
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 !