2 Replies Latest reply on Oct 13, 2018 10:43 PM by syama tunuguntla

    lookup and global filter

    syama tunuguntla



      i have a problem making a field that uses lookup function and make it available as a filter across multiple sheets.


      my goal is 1. by using the username function i can implement row level security in association with zipcodes. 2. every zip code is in a state so would like to provide state as a global filter so user can select states.




      table 1 has zipcode and username

      each user has a number of zipcodes assigned


      table 2 has large list of fields including zipcodes

      blended both of them using zipcodes and this table 2 is primary table


      used username = username() function so that i can filter data based on who logs in to the site which is working fine based on table 1


      Now the problem is : Each user can have assigned zipcodes across multiple states. and table 2 has state field. when i pull the state field to display i can see all the states 50 of them

      so i used a lookup formula : lookup(min(states),0) and this showed me the correct states based on the zip codes assigned to each user. this acts as a measure for sure.


      Because i have multiple sheets in the dashboard and users can have multiple states (based on zipcodes) i want to be able to show this field as a global filter. currently it works on single sheet only.


      Thanks in advance


        • 1. Re: lookup and global filter
          Jim Dehner


          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.

          • 2. Re: lookup and global filter
            syama tunuguntla



            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.


            Thank you