Data table relationship to multiple facts table

[spartanboy]

Hello, 

I have a multiple star schema or a galaxy schema. What I mean is, there are multiple dimension tables and multiple fact tables with different granularity, all arranged or related like a galaxy schema- I have made sure none have circular relation, all have many-many relationship (because I was getting blank in slicer) and both direction (so that I can slice and dice in any way).

Now, I have to introduce a 'Date' table which includes: Month, Year, Type (that classifies, weather the date is history or forecast). I am only able to have relation to any one of 4 fact tables. Unable to have another relationship as it become circular and relationship gets inactive. How can I introduce this shared dimension to all the fact tables?

[spartanboy]

@vanessafvg:

Attached is the model:

To summarize, all the dimensions table are hirearchy of products, and suppliers. The facts tables are at different granularity.

When i create a table with 'Type, Date, Lower Product IDs, Facts' - I get the required results
But, while creating a table with 'Type, Date, SKU IDs, Supplier IDs, Facts' - I get duplicate Dates and Type (ie. history Jan 2021 and forecast Jan 2021)

[Anonymous]

Hi @spartanboy ，

Can you please share the raw data of the corresponding table fields applied to the table visual with duplicate date and type? Are these fields from the same table? If not, were any relationships created between the tables of these referenced fields? Besides that, please provide your expected result with backend logic and special examples. Thank you.

Alternatively, you can refer to the approach in the link below to get the correct results.

Inactive relationships

Orders Shipped =
CALCULATE(
    COUNTROWS(Sales)
    ,USERELATIONSHIP('Date'[Date], Sales[ShipDate])
)

Get a field value from a related table in Power BI: DAX RELATED Function Explained

How to use LOOKUPVALUE to match data between two tables

Best Regards

[vanessafvg]

please share your current relationship view, i am  assuming you have a bi directional relationship that is not allow this to be created as a primary relationships.  You will next to explain what you are trying to do with the bi directional relationship.