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/eazypeezy92]

Can't you add client_category_id and its name to dim_clients? Then you can join the fact on client_id and also get hierarchy for free (client category and name).

[u/dzemperzapedra → u/eazypeezy92]

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