Dimensional modeling - ambiguous relationship

Question

I tried to solve the problem, and so far I have not been able to achieve what, as I said, is the optimal solution. I have a dimension (function) that needs to be referenced in two other dimensions (Actions and Sessions), which, in turn, refer to the same fact table (UserAction). This creates ambiguity, and I cannot complete the circuit:

(note: a snapshot of the model, not all) (including bridge tables to show some additional complexity in a model with many relationships).

I think the problem may be that Dim_Features technically have different meanings between both dimensions, but am I still trying to use it as one and the same? It means that:

• The action belongs to this area of ​​functions / functions.
• The session had an available Feature / Feature Area.

What I need to accomplish is the ability to filter / cut Fact_UserActions in sessions where some functions are available / inaccessible, then to analyze things like:

• What functions are used when property “A” belongs (such as, for example, the correlation between some functions that are owned and others)?
• How many users who own the feature have not used it?
• How often is the function used? (constrained by the totality of sessions that own it, i.e. where they can really be used).

Any ideas on what I can do wrong, or how can I improve the model?

EDIT: in case this helps, then what we would like to get is a table, for example:

Where can we see the effect that the function has on the population as a whole, and on the population that owns it.

Answer 1

I think your problem is that you are working with the wrong grain. Kimball's standard advice for stellar designs is to always find absolute low grain, because you can always come together.

Look at all the questions that you want to answer - they all relate to the use of functions, but the fact table that you use to analyze functions is not at the level of use of functions. Bridge tables exist to try to get around this.

It is important to remember that the vast majority of the time your measurements should be indirectly related, albeit to fact tables. Sometimes you need a Bridge table, but relatively rarely.

It is difficult to come up with the proposed scheme here, not knowing how it fits into the rest of the model, but consider the following:

• Replace Fact_UserAction with something like Fact_FeatureUsage.
• Have action_id, session_id and feature_id in Fact_FeatureUsage.
• Get rid of Bridge tables.

Answer 2

I would delete the relationship with Dim_Features and then hide them.

Then I would create two new tables (while in the "Report" or "Data" view, go to the "Simulation" ribbon and click "New Table"). DAX expressions for each will look something like this:

Features (Actions) = 'Dim_Features' Features (Sessions) = 'Dim_Features' 

Now you have 2 independent copies of the Dimension table, and you can create relationships for each of them in the Links window.