cancel
Showing results for 
Search instead for 
Did you mean: 

Dimension filter on id not working correctly

zach_myt
10 - ETL
10 - ETL

I have a dashboard with a dimension filter. The dimension filter table is a list of organizations and the products associated with those organizations. I have a widget displaying organization and product buy counts. When I filter on a product ID that is only associated with one organization it is affecting the counts of organizations that are not link to that product ID. 

If I filter for product ID on the fact table where I pull the buy counts from it works fine, but I want to use the dimension table so that it is filtered other places in the dashboard as well. I have verified this product is only linked to one organization. Why would a dimension table filter affect organizations that do not have that product ID linked to them?

1 ACCEPTED SOLUTION

zach_myt
10 - ETL
10 - ETL

This was due to the dimension table not containing all values from the fact table. Our dimension table only list active data but the fact table also includes data that was active once but is now inactive. For now, my solution was to use widget level filtering. I believe in the future we will look to add the inactive data with another column to identify if the data is active or not.

View solution in original post

2 REPLIES 2

david-h
9 - Travel Pro
9 - Travel Pro

Hi Zach,

Your question can not be answered without having more context, especially data model and specific dashboard widgets related to your filters.

It reads like an issue called "random query path". Are you familiar with this concept ? Is you data model a pure star schema with only one fact table in the center of the model or a data model with several fact tables and dimension tables ? If this is the latest, are you following the best practise rules ? Basically:

  • link all dim to all facts,
  • masking the join fields from the fact table
  • always filter from dimensions and never use calculation from dimension (ex. : counting products from product id in the dim table instead of using one of the fact)

If you are using the JAQLine plugin ? It can translate a widget query into a visual diagram explaining the precise tables and joins involved in the query.

Let me know if this helps.

Best, David.

zach_myt
10 - ETL
10 - ETL

This was due to the dimension table not containing all values from the fact table. Our dimension table only list active data but the fact table also includes data that was active once but is now inactive. For now, my solution was to use widget level filtering. I believe in the future we will look to add the inactive data with another column to identify if the data is active or not.