Slow calculated column is made faster with a calculated table -- but need to find alternative option

[champioz]

I have a somewhat complex issue:

-LiveQuery dataset that is too large to import, so since I need a scheduled refresh calculated tables not really an option

-Given a list of ID -> category mappings for one of the dimension tables to define a calculated column for use in filtering. First implemented it as a big ugly switch statement based on substrings of the ID. Even with some logic cleaning as seen here I'm getting 30+ seconds to slice using the calc columm directly.

-Out of curiosity tried a calculated table to reduce the search space to just the subset of IDs that are getting mapped to a category. After giving it the necessary relationships, using the calculated table's copy of the calculated column to slice the ORIGINAL table is extremely fast and snappy. But as mentioned this is not a viable solution due to refresh. I'm not getting the same results when I reduce the search space instead via either a second, preliminary calculated column or properly ordering the logic in the main column to wipe out the irrelevant IDs first.

Why is this happening, and is there a way to get this sort of performance without the need for a calculated table? Logic below for illustration.

-------

Scenario: first, filter to parts that contain d_. Then, this subset has multiple categories based on substring. Doing this all in one calculated column is unusably slow, separating the first step into a calculated table makes it very fast.

The two original tables, the ID dimensional table and some fact table:

     

The Calculated column formulae:

   

The Calculated Table with formula:

And the implementation/results -- the table visual here is populated with the original dimension table and the fact table(s):

Any insights are appreciated!!!

[lbendlin]

LiveQuery dataset that is too large to import, 

Have you considered partitions and/or incremental refresh?

[champioz]

That's a good idea -- I'll have to talk to our data engineer but that would be much preferable to hacking together something that works with limited calculations.