I'm not totally following the procedure - but the problem is Lookup() is a table calculation that works on the underlying table for the worksheet - as such it can not be based for sheet to sheet (they have different underlying tables)
so what to do - is it possible to use an LOD expression to relate the user/zip code - a fixed LOD will result in a dimension that can be passed from sheet to sheet -
the way fixed works it creates permutations for the all the dimension combinations that precede the colon eg and then aggregates them by what follows
If you had the user name and zip code as individual dimensions fixed would return a value for each zip that has a corresponding user name - I'm not certain of the logic and what your are trying to match (I don't see where you correlate zip and state)
see below - superstore example - the second columns is the result of the fixed formula converted to a discrete dimension - I think that mimics a lookup
if I put in a worksheet action the fixed zips can be sent from one sheet to another
this is the starting sheet
select a user and the to sheet receives this
If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution. Thank you.
zip code lookup.twbx 2.4 MB
Appreciate your quick response. let me try explain in a better way. i did not do a good job before. i have Table A (comes from spark) which has a ton of data and extract is huge as well even with hyper format. the customers scattered around usa so we duplicated the workbook based on zones like East, west etc.and provided access to group of users that are in East and West. Now i am trying to provide user level security and use a single workbook.
so i tried to create a new table Table B (comes from SQL server)with all the users and zip codes. i had to use zip codes here because previously we used county names and now we want to zipcodes as LOD but still give users ability to select the couty names using filter.
Now Table A has county names, zipcodes, single measure along with a long list of fields. users are used to click on county names . i joined table B and it is a secondary source because i used blending.
then created filter with username = username() to and i was able to see user level security working BUT county name filter does not cooperate now this is the reason i used lookup function but it is working but limited to a single sheet.
how to keep the county name as a global filter and still filter data based on who logs in.