In multiple fact tables, that all contain a common field, when filtering on this field in a dashboard, you want the filter to be independent of all of the related fact tables.
In order to accomplish this, you’ll need to generate a dimension table for this field. To go about this, would be to create a separate Dimension table for the field using a Custom SQL Expression. Then pull the field from the Dim table into the filter and NOT from one of the Fact tables.
Please find below the steps to accomplish this:
- Create a new dimension table using a Custom SQL Expression (example below)
- Name the table in such a way that it will be easily identifiable. For the example of dimension Order and name the table dimOrder. Define the table with the following SQL expression:
SELECT DISTINCT [Order] from table1
SELECT DISTINCT [Order] from table2
SELECT DISTINCT [Order] from table3
*Note: Extrapolate this methodology for as many fact tables as contain this common dimension.
- Make sure to replace table1, table2, table3 with the actual names of your tables.
- Connect the dim table to the other fact tables via the dimension field.
Rebuild your ElastiCube.
Finally, in your Dashboard, pull the dim table field into your filter.