Is this a filter in general or a security filter? What is its purpose?
If there are useless values in your data warehouse, why are they there?
Is this filter in the query subject? Is it a stand alone filter?
When you test the query subject in FM, does it work? What results do you get?
If the table [Transformation layer].[TableAcceptableValues].[Column_Values] has only one column, what do you use as keys?
Is each thing which is allowed a separate record or is there only one record with a value of
'this', 'that', 'whatever' (given that the expression you show has [Transformation layer].[TableAcceptableValues].[Column_Values] in brackets)
I want to filter out rows from several tables.
Where are things which you want to use to filter the other tables?
How is this possible?
Can't say as I have not encountered the problem. I use data security filters where I use the user id session parameter in a macro to lookup expressions of what data particular users are allowed to see, which is the technique I was taught.
As soon as the filters are added, the report breaks and the data elements from the queries go missing. The report runs, but the illegal rows pass through,....
"data elements"= query items? Filters? Plural? If the report breaks, how can it run?
Also, usually error messages are accompanied with a text message. Some times the message actually tells me what I've done wrong. Just saying
I got error # x doesn't help the cause that much, especially as there isn't a table of error messages hanging around, some error message numbers appear for several different errors, and the error message text, as I said, can actually tell us something.
What the heck is a "transformation layer"? What's wrong with the ol' query, business, dimension, presentation layer cake?
Start the trouble shooting by investigating in FM and controlling for that.