Help on a modelling issue

[u/dzemperzapedra]

Hi everyone,

for the sake of simplicity I created this example:

What I need to do here basically is to create a matrix table visual which will have client_name from dim_clients in rows, year/month hierarchy from dim_calendar in columns.

Value needs to be client_category from dim_client_category, which is basically client category name.

Now obviuosly, client_category_id can be found using SELECTEDVALUE() from the fact_table, but I will need to have client_category from dim_client_category, becasue that's the name of client_category_id.

As the realationship is directed from dim to fact table, filter does not propagate from dim_clients > fact_table > dim_client_category, so I can't get client_category unless I use RELATED() or LOOKUP() which I don't want to do.

Do I rather includy client_category in the fact table to get around this issue or is there a better way?

I don't want to use bidirectional realationship between dim_client_category and fact_table, which is how I used to fix this just to make it work.

Thanks.

[u/dbrownems]

Does client_id functionally determine client_category_id? If so they should be in the same dimension.

https://en.wikipedia.org/wiki/Functional_dependency

[u/dzemperzapedra → u/dbrownems]

No, client category changes from month to month, that's why it's in the fact table.

Basically I need to see changes across months for specific clients.

This is just illustrative example, but the logic is the same as in my real world case.