Lookup hell... in over my head...

[jeffminder]

Trying to create a slice and need expression which does the following:

Use value of: Schedule[Range] to cross reference "Range Directory"[Range] and get the corresponding "Range Directory"[vState] value. This returns the two-letter state abbreviation. The tricky part dynamically referencing a column name based on the result of the first lookup.

Next cross reference "Range Directory"[vState] with "Range Filter"[<two-letter-state-code>]. if this value is true, return true, otherwise false.

IF( LOOKUP( LOOKUP(Schedule[Range], "Range Directory", [Range], [vState]), "Range Filter", [<two-letter-state-code>], [Value] ) = true, true, false )

I'm stuck on how to do the 2nd lookup, using the value of the first lookup to build the value of the query. The structure "Range Filter" table is a two-letter state code for every state as the column name. For example: "Range Filter"[AL]='true'.

The reason for making states the column names was this was the only way known to use the quick-edit buttons in the UI to make it very simple. All the user sees in the view is: <State Code>: ON/OFF on a single row for each state.  Structuring the table in any other format prevented this clean/simple looking view.

[IT_Innovations]

Have you tried the putting the data in a table view and allowing the user to select based off the filter drop down option?

[IT_Innovations]

The issue with Slices is it isn't compatible with what you are doing. Slices are great for narrowing down a table of data row by row.

Row filter condition

True/false expression that checks if a row should be included in the slice

Therefore, your formula has to return true or false or it won't work. It simply is saying that row should be included in the slice or not. That is all. So for instance [State]="TN". If the state was TN it would be in the slice. If it wasn't TN it wouldn't be in the slice. Then you could create a view with that slice and it would show all the data where the state was TN. What it sounds to me like is you need dependent dropdowns using validif statements. In this case one field is dependent on the other fields current data. Also, I couple that with showif statements so the next field doesnt show until there is a value in the first field. 

If you need help I can view your app and provide assistance free of charge. You can make a copy and I can work on it for you. (PII Removed by Staff)
There is probably a solution. I have created complex queries in the past that were dynamic like you are trying to do.

[jeffminder · Accepted solution]

This works!


SWITCH(
LOOKUP([_THISROW].[Range], "Range Directory", "Range", "vState"),
"AL", INDEX(Range Filter[AL], 1),
"AK", INDEX(Range Filter[AK], 1),
"AZ", INDEX(Range Filter[AZ], 1),
...
...
"WY", INDEX(Range Filter[WY], 1),
FALSE
)

[IT_Innovations]

Wonderful. It looks like it's pretty taxing though.

[jeffminder]

No doubt, It ain't pretty...  

The only reason why 'Range Filter' table was structured this way was to enable quick-edit buttons and make page which enables/disables each state as simple as possible, resulting in this view:

[Trevwiller]

IF the slice condition is 

IF( LOOKUP( LOOKUP(Schedule[Range], "Range Directory", [Range], [vState]), "Range Filter", [<two-letter-state-code>], [Value] ) = true, true, false )

I see some formatting issues "Schedule[Range]" is a list. A LOOKUP() is meant to look up a singular value in a list (in this case the Range column of Range Directory.) IF this slice is inside of the table Schedule then we need to adjust as below. (not correct yet)

IF( LOOKUP( LOOKUP([Range], "Range Directory", "Range", "vState"), "Range Filter", "<two-letter-state-code>", "Value") = true, true, false )

Further breaking it down with the nested look up we end up with a value at the end that I assume from the set up that Value is a yes/no column. IF this is the case then we can trim a few items. remove the IF() as for a slice unless you need to check two differing parameters for a slice to prove the same outcome. 

LOOKUP( LOOKUP([Range], "Range Directory", "Range", "vState"), "Range Filter", "<two-letter-state-code>", "Value") = TRUE

We need to imagine a slice statement on the row level and what rows we want to pull. I want to pull rows that meet my condition only. 

Slices will need to sync to display the data you are trying to pull. So when you make your rocker switch selection of statecode the user will have to hit sync or you can do research on a force sync, or make the app sync on change. 

Things to consider:

IF the table we are making our selection from is one row this app can only be used comfortably by one user. If I choose TX look at the data provided - Then another user selects NM - When I sync again I will see NM schedule. 

IF this is based on a per user then you will have no issue but would require a row of choices per user that the user could only see their row. So on the Schedule table would need to be a column with USEREMAIL(). Then security filter the rows for this table to be [User Column] = USEREMAIL() . There are other options for user Refs, but require more setup.

[Steve]

---

@jeffminder wrote:

The tricky part dynamically referencing a column name based on the result of the first lookup.

---

You can't do it using LOOKUP(). You'll have to use SWITCH() in SELECT() instead. Something like this, maybe?

ANY(
  SELECT(
    Range Filter[Value],
    SWITCH(
      LOOKUP(
        [_THISROW].[Range],
        "Range Directory",
        "Range",
        "vState"
      ),
      "AL", [AL],
      "AK", [AK],
      ...,
      "WY", [WY],
      ""
    )
  )
)