Opinions and technical solutions greatly appreciated please.
I am building a SQL database query based on query parameters from Power BI Desktop. Users will enter the parameter (text value) which will pre-filter the data brought into the dashboard. Nothing controversial about that.
However, the parameter affects a single dimension table from the database. I have other dependent dimensions, and of course the fact table, that I would like to filter as part of the query too. The affected parameter field is not present in those other tables.
So, I have two options:
1) Amend my SQL queries to include the parameter field in every imported table, and 'filter rows by parameter' in Power Query;
2) Leave the SQL queries as they are, and use a merge function (inner join) on each of the other tables to return only the rows that match the table filtered by the query parameter.