Hi Guys, apologies for the length of my post but I really need some help.
My organisation has begun building a new Data Warehouse that doesn't quite meet the needs of our reporting, here is my situation:
I am building a Power BI data model using the new DW, I have two fact tables (Opportunity and Quote). Opportunity can exist without a Quote, Quote can't exist without Opportunity. On our reporting, we need to show Opportunities and their associated Quotes (one Opportunity can have multiple Quotes but only one Quote can be Master)
If I was designing the DW I would have Opportunity and Quote dimension tables and I would also have Opportunity and Quote fact tables:
OpportunityDim would join to OpportunityFact
QuoteDim would join to QuoteFact
OpportunityDim would join to QuoteFact
However, this is not how the DW has been designed, we do not have dimension tables for Opportunity or Quote, instead we have fact tables that include both dimension and fact data. Our only option would be to somehow use DAX to get associated records (opportunity id exists in both tables so we could use this). Is this a really bad idea?
Just to be clear, I'm not suggesting I want to join fact tables directly using opportunity id, I know this is a bad idea, but is doing it in DAX really any better?
I have a chat with my manager later and I need a way of explaining this better 🙂
