Hi.
Is it possible to create a formula that filters a list of values in a helper table by a column value in the original filtered table?
*“Helper table” = a table that contains a list of filtered values from another table
Description
I have a table with all the raw data (“Schedule”). It contains lookup columns from helper tables (different categories for the data).
The helper tables have a column with a filter formula for the rows from the schedule table.
I also have another table that is used as a dashboard and data analysis tool: “Analysis by Operator and Type”. It has a single row.
Right now I’m filtering the data in the analysis table from the schedule table directly: Schedule.Filter(Operator.contains(thisRow.Operator)AndType.contains(thisRow.Type)And[Stream Type].Contains(thisRow.Stream))
What I really need is to get the data from the helper tables, and filter out the some of the values inside the column containing the list of values from the schedule table.
Example:
In Analysis table I choose “Stream”: “Live” in the Stream select list column.
The “Genre” column will then propagate with the genre rows from the “Genre” table. The 2nd column in the Genre table is “Show” - a list of filtered rows from the raw data (“Schedule”) table.
So the “Genre” column in the Analysis table will get the info from the Genre table, including only shows that their “Stream” tag is “Live”.
Here’s the test table for reference
Thank you for all helpers
Hi @Premshay_Hermon,
Yes, it is possible.
Basically, you can lookup from a view that is filter of a table and ultimately creating a Filter chain either with formulas ([Table].Filter(condition).Filter(another condition from filtered data).Filter(and so on)) or through pre-calculated variables (e.g. in columns).
What is not fully clear to me, though, is that you have multiple dimensions in your filtering, all of then eventually would lookup on the Schedule table.
If Schedule table has Operator, Broadcaster, Type and Genre columns you can have filtered views by any of those.
But if you need the full orthogonality of your dimensions I can’t see how an “helper” table might - actually - help.
One trick to try (I didn’t) is to create a chained conditional filter, so that every condition would activate that dimension or not.
something like:
[Schedule]. Filter(Operator.Contains(If(thisRow.Operator.IsBlank(),CurrentValue, thisRow.Operator))). Filter(Broadcaster.Contains(If(thisRow.Broadcaster.IsBlank(),CurrentValue, thisRow.Broadcaster))).
(etc…)
But I have the feeling I could have misunderstood your question 
Let me know if I’m far away…
@Federico_Stefanato,
I’ll try that, thanks!
The reasons for going through helper tables are:
- It’s the only way I found to make multiple-select values referenced correctly.
Ex.: show #1 has 2 genre values: “Classical” “N. Africa”.
Coda normally counts it as its own category: “Classical, N. Africa”, instead of counting it once in “Classical” and once in “N. Africa”.
The helper table allows this to happen correctly.
Take a look again at the table, or see the image, I’ve written each formula used in it:
Posts in the community affirm this is the only current solution, and that’s why filtering directly from Schedule is a problem. If you have a different approach, I’ll be happy to try.
- It lets me create percentage comparisons in separate columns (see Broadcaster Table or Stream Type Table) and present that data, and then present it as a graph on the dashboard.
Again, if you know of amore efficient way for that, I’ll be happy to learn (as will others).
Hi @Premshay_Hermon,
Now I see what you meant.
You are right: lookup columns with multiple values are not considered as single counts.
I don’t know if someone else came up with different solutions.
I’ll take some time to experiment a bit.
Thanks @Federico_Stefanato.
I tried your solution:
[Table].Filter(condition).Filter(another condition from filtered data).Filter(and so on)
Unfortunately it didn’t work. I think it’s because it doesn’t mutate the data in the table, so when it only filters rows that don’t meet the condition. If a row has a value that fits the condition, it passes the entire row and all its values. i.e. if I choose to filter by Broadcaster A, all the shows in the row “Classical” of the Genre table will pass, even if only one show was from that broadcaster. BTW, the “Show” column there is a function for initial filtering on that “Show” column, as you also suggested. I believe your solution for chain filtering can make it redundant, which is a good refactoring. Unfortunately it doesn’t work as intended yet.
Happy to hear your thoughts.
Thanks.
hi @Premshay_Hermon
Yes, it does not.
I just realised that after your second post, where I actually got the whole point…
Sorry for not being resolutive.
I’ll think if there are some smarter alternatives; thus far your solution seems to be the best one.
Thanks @Federico_Stefanato.
It was a fruitful discussion nonetheless.
BTW, liquidprice.ai (your startup) looks really cool and valuable. I put it in my favorite SaaS list 
1 Like
Thanks a lot, @Premshay_Hermon: really nice of you 
Happy to share thought and (tough!) experiences about it. 
