Hi,
Tearing my hair out here...
I have a table with a [team name] column. These team names are hierarchical codes;
- Team 1
- Team 1a
- Team 1ax
- Team 2a
- Team 2bx
- Team 5dzp
- ...
I have an enumlist field that I want to use to allow for filtering of the data. I have it working fine for explicit selections (select every single team you want to see), but the tool users would prefer that selecting 'Team 1' would show 'Team 1' and all sub-teams ('Team 1a', 'Team 1bz', etc...)
So I am trying to understand how to create this as an expression. It's almost a backwards IN? If any of the values in the enumlist are IN the [team name] value then true. Any smart ways of doing this that aren't going to have a significant impact on app performance? Otherwise I'll apply some pressure on the users to just accept what I have working now!
Thanks
Hi @Josh_B
Have you tried looking into CONTAINS()?
Maybe one of the more knowledgeable forum members might be able to advise you better than I, but I believe I have used this in similar applications in the past.
I think with more details , a more definitive approach could be suggested by the community. For example, will the team name also have
Team 50mnj
Team 50hjg
meaning 2 digits or even 3 digits to follow the word 'Team" . There will need to be a known pattern to extract a "common elements" in a team name.
Secondly, you may want to share where the enumlist lies, same table or the different table and how it will be constructed as more teams get added.
Anyway with the current details, the approach could be as follows. For the currently given names, the pattern is, first 6 letters denote the common names of the teams.
Create a virtual / real column called say [Team_Name_Pattern] with an expression something like LEFT([Team Name] , 6) So it will extract the words "Team 1" from .
- Team 1
- Team 1a
- Team 1ax
and "Team 2" from
- Team 2a
- Team 2bx
and so on.
Then the expression to select team names from enumlist could be
SELECT( Table Name[Team Name] , IN( [Team_Name_Pattern], [Enumlist Column]))
[Enumlist Column] is assumed to have options such as {"Team 1", "Team 2"......"Team 5"} and so on. This means the enumlist contains the first 6 letters of each group of the longer team names.
Depending on how the data structure is a slice could be created to select the desired team names based on the user's selection in another filter inputs table instead of the sync expensive SELECT()
Thanks Suvrutt,
Some more details;
- teams will always start with a number and then between 2 and 5 additional characters.
- the [enumlist] field lies in a separate table, storing the comma separated values in a row specific to the user. The table contains a variety of user selected 'filter' values. I have a slice of this table that allows me to easily get the selected values for the current user.
- available options to select in the [enumlist] field are defined by SORT(UNIQUE([Team Name])), so that any team that has data within the tool can be selected.
- I'm hoping to use this expression to build a slice that just includes rows with an appropriate [Team Name].
If I follow your suggestion, I will have the hierarchy for each row in the data explicitly defined. If the [Team Name] is 1awdbq then I will have additional columns in that table containing 1awdb, 1awd, 1aw... etc...
I then have the value in my [enumlist] field where the user has selected the teams they want to see, e.g. "1aws, 1awd"
So I want to perform a check to see if either '1aws' or '1awd' is in any of those columns. This would require an OR statement (rough expression below, because I'm not respecting table names, slices, etc...);
OR(
IN([Team Name],[enumlist]),
IN([Team Name - 1 character],[enumlist]),
IN([Team Name - 2 characters],[enumlist]),
IN([Team Name - 3 characters],[enumlist]),
...)
I suppose this would work, but I'm not sure if it's a particularly efficient way of performing the check? Happy to try and implement and see how it goes!
Thank you for more details.
I think with the flexibility of 1, 2 or 3 characters being given to the user, your approach seems to be good.
User | Count |
---|---|
33 | |
11 | |
3 | |
2 | |
2 |