Hello, I am new to the forum.
My problem is somewhat general. I have multiple large dimension tables (one with 14 million rows) linked to a fact table. In order to reduce load time and table sizes, I would like a query editor method to filter the large dimension tables to contain only records where the key field is contained in the fact table.
If I set the cross-filter direction to both, I get the desired result. However, this seems to always be discouraged. The reasons for not using ‘both’ for the relationship seem to be (1) if there are multiple fact tables there could be issues in resolving the filter back to the dimension, (2) Poor performance
I also tried merging the dimension table with the fact table using an inner join and then removing the fact table fields, but this option takes a very long time to process.
This would seem like a common problem when record counts are high, however, I have been unable to find a best practice solution in the courses or elsewhere.
I would appreciate information on any best practice solutions for handling this problem.