Multiple Dimensions, Multiple Facts Data Model

[browncp88]

Is it possible in Power BI to model multiple dimensions and multiple facts?

To simplify from first-principles, imagine I have two fact tables:

1) Stock Data Time Series (Asset Class, Date, Price to Earnings Ratio, +20 other Stock ratios)

2) Bond Data Time Series (Asset Class, Date, Yield to Maturity, +20 other Bond ratios)

and two dimension tables:

1) Asset Class MetaData (links to stock and bond data by asset class, or asset class index)

2) Date table (links to stock and bond data by date, or date index)

I have issues autodetecting these relationships, and get errors activating the relationships:

I think this would be a standard schema for database normalization, so am curious if this is possible to model in power bi, or if I need to adjust the data model?  Ultimately, I intend to scale this to ~10 fact tables (1 per asset class) and two dimension tables.  

My goal is to get time series data to compare ratios across the two different fact tables on a single visualization (using the date table for common lin), rather than 2 separate tables as below:

I know I can do this if I remove the asset class table and just link the two fact tables to the date dimension, but I also need assetclass dimension metadata pertaining to all fact tables (e.g., risk level) necessary for data visualization purposes.  

Any modeling advice would be appreciated (maybe I'm not thinking about this the right way), thank you in advance.

Thanks!

[Jaaap]

For ways to do this seee https://maxpowerbi.pro/facts-facts-facts-five-ways-of-combining-several-fact-tables-in-a-powerbi-rep...

[DataInsights · Accepted solution]

@browncp88,

Yes, you can model multiple dimensions and multiple facts. Create a relationship between each fact table and the dimension tables as shown below:

You can add as many fact tables as you need. In a visual, use fields from dimension tables and you'll be able to see measures for each fact table since the fact tables share dimension tables.

[browncp88]

Thanks, your graphic showed me the issue I was having - needed to have single direction cross filters rather than bi-directional, which was selected.  Appreciate the help!