Hi All,
For one of our data models we have a fact and a dimension table with a many-to-many relationship. In short there are multiple users in a fact table linked to a dimension table which contains the dimensional data for users. This dimension also contains userTags, where one user can have multiple tags. I want to be able to filter on one or multiple tags and if I simply relate these tables in the model with the current many-to-many relation it gives me the problem that if I filter on multiple tags it counts the information of the users who have multiple tags double (1 for each selected tag).
I am trying to solve this by implementing a briding table. A sketch of the data model can be seen in the screenshot:
I have set these relations in the data model. Where user_id is the foreign key in the Fact table and the entity key of the userDim. User_id and tag_id are both set to foreign key in the tagUserBridge and tag_id is the entity key of TagDim. However When I apply a filter I only get the columns of the DimUser table as filter options (not the tag names), I have also created a screenshot of the filter options:
Am I missing something, or am I doing something wrong here? Or is there just no way of creating a model like this in metabase?
I hope someone here can help me out or point me in the right direction.
Thanks in advance!
Nick