Fact table with multiple dimension relationships

[markca]

I'm struggling with what I think should be a straightforward concept, but I'm not a data scientist so maybe I'm approaching it wrong.

I've manipulated our data into (what I think) is a clean structure for reporting in BI - a single fact table with multiple dimensions.

The Fact table has multiple dates - lead date, sale date, completion date, paid date... linked with one active and multiple inactive relationships.

Same for the User table - lead agent, sales agent, referrer etc.

I've set up RLS so that users can only see records relevant to them (should be on an OR basis - i.e. if they're involved in any of the columns, then they should see the row).

There's lots of content online about multiple dates in fact tables - and the most common solution seems to be to use USERELATOPNSHIP in a measure. I'm fine with that on some metrics, but I want to show (For example), the number of leads an agent has had and then list pertinent columns in a table visual.

The measure for the count works fine, but the table visual resorts to the default relationships (as you'd expect) for the date and user, so it's showing the wrong data.

What I want to do is an equivalent of USERELATIONSHIP on a visual but that doesn't seem possible and I don't know how to get around it.

One option seems to be to duplicate the dimension tables for each relationship, which seems unnecessary, but ok. My problem with that is I'd need to run up RLS rules on all the user tables and they will then work on an 'AND' basis, meaning they'll see nothing.

As I started - it seems simple. I want to show the 'journey' in a series of visuals, highlighting pipeline, burn rates etc.. it must've been done millions of times before... what am I doing wrong?!

[markca]

Thanks @Anonymous for taking time out to reply and assist.

I'm not sure that I fully understand. If I add these columns and merge data from the fact table to each, then they grow huge and would become fact tables in their own right?

Unless, the 'calculated columns' mean they are dynamic in some way, but I didn't believe that was possible.

Is there a simple example somewhere that demonstrates your process so I can try and learn more.

[Anonymous]

Hi @markca ,

For each of the dates (lead date, sale date, etc.) and user roles (lead agent, sales agent, etc.) in your fact table, you can create calculated columns in your dimension tables that fetch the relevant information based on the active relationship. This might involve using or functions. While this approach might seem cumbersome, it allows you to bring in the necessary context directly into your dimension tables, which can then be used in visuals without relying on

Best Regards,

Xianda Tang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.