Help modelling relationships with 3 fact tables and 3 dimensions

[Anonymous]

I have the following model setup:

Fact B table is interconnection between A and C, but A and C are completely independent of each other. What this means, is that we do reports on Fact C independent of data in Fact A and we do reports on Fact A independent of data in Fact C. When we do reports on Fact B, Fact A and Fact C are used for filtering, so they become dimensions in that case. 

With the current setup, if I report something on Fact A (e.g. an aggregate), and I have Dimension X set as a report level filter, if there is no corresponding row in Fact B for a row in Fact A, the row in Fact A is not included in the calculation. Which is an expected scenario for my model setup, but not for my use case.

Now, I'm thinking that I should import Fact C and Fact A tables twice, and the second time renamed them into Dimensions. Then, the Fact tables should never be interconnected with each other. Is this a good approach? I want to keep Dimension X and Dimension Z as common filter for ALL fact tables reports, so I can use them as global page or report level filters. Use them as slicers as well.

Thanks!

Gorgi.

[Greg_Deckler]

Could you post some sample data from your fact tables and dimension X table? You might be able to achieve what you want by editing the cross filtering settings for your relationships.

[Anonymous]

I cannot share any data or structure, but lets say that Fact C is a customer, Fact A is an instance of a product in a specific Dimension X and Dimension Z is a product information. Fact B would be customer interacting with product instance C. The Fact C contains summary data for the product instance, like turnover. 

Fact C (the customer) is unique and lives in a dimension X. So, it also contains summary information for the customer, like total turnover. 

[KHorseman]

@Anonymous you don't have to share real data, just an example so we're working with the same kind of materials as you. https://www.mockaroo.com/

[Anonymous]

Thanks for your help guys. I will try to do that by tomorrow.

Cheers!

[Anonymous]

@KHorseman @Greg_Deckler

here is a one simplified example: https://www.dropbox.com/s/f8xmg0r8pvkxxao/Test.pbix?dl=0

From the diagram above, I'm only missing Dimension Y, which is a time dimension. But, the Facts here are Customer, Product Instance and CustomerProductInstance. A customer can exist in the Customer fact, without corresponding CustomerProductInstance.

Looking forward to your help!

Gorgi.

[v-haibl-msft · Accepted solution]

@Anonymous

In your current relationship, there is an inactive relationship between Project and ProductInstance. You only need to create a ProjectId measure which using the inactive relationship to replace the ‘ProductInstance’[ProjectId] column in the table visual.

I've uploaded the PBIX file here for reference.

Projectid_Measure = 
CALCULATE (
    MAX ( ProductInstance[ProjectId] ),
    USERELATIONSHIP ( Project[Id], ProductInstance[ProjectId] )
)

Best Regards,

Herbert

[Anonymous]

Thanks @v-haibl-msft this helps. It even works when I add a demographic slicer, so it shows products that have been interacted with specific demographic.

One issue is that I have to bring the Projectid_Measure in the reports, so the relationship is included. 

But is this approach recommended? One solution suggested here: (scroll a bit up and see the two bullet points) is to "Bring in a table twice (with a different name the second time) to eliminate loops.  This makes the pattern of relationships like a star schema.  With a star schema all of the relationships can be set to Both.". But, this approach is more complex and doesn't work good with page level filters (and slicers).

I will try this solution to our specific model and see how it works.

Cheers.

[v-haibl-msft]

@Anonymous

You can try both of these two most common approaches to see which is more suitable for you. 

Best Regards,

Herbert