Data Modeling help

[chris.turnbull]

I have 2 FACT TABLES that have shared Dimesion tables as the Diagram.

factWidget Contains the Number of Widgets that have been sold by Type, Dataset, Date and Region and factMoney has a single line for cost for each Dataset, Date and Region combination so it is a bit like a header detail senario.

How do I relate or model the 2 tables so that I can have them on the same pivot? Id rather not flatten them as I cannot get hold of the Line Costs.

An example is where I might want to SLICE BY a Date of today with a region of Europe and Dataset to create a Matrix or Table that gives me :-

05/03/2018 Europe £1000 (This is the Value from factMoney) Big (Type from Fact Widget) 5000 (Widget Total Sold from factWidgets)

05/03/2018 North America £1090 (This is the Value from factMoney) Medium (Type from Fact Widget) 2000 (Widget Total Sold from factWidgets)

05/03/2018 New Zealand £10090 (This is the Value from factMoney) Small (Type from Fact Widget) 2000 (Widget Total Sold from factWidgets)

Another Scenarion might be:-

Date Region Small Medium Big Total

05/03/2018 North America 500 400 200 £1080

Hopefully this makes sense, I have been fighting the beast all day and have just hit the DAX Wall so any help gratefully received.

Thanks

Chris

[sam.mckay]

Looks to me like the only problem here will be the ‘type’ column from the fact widget table as there’s no relationship.

Is there a relationship though at all because the money table doesn’t have any type column or anything related to that.

Can you show me what the table looks like?..the results. I’m struggling to understand how any filter would work as the factMoney table doesn’t have anything as per ‘type’.

Maybe the TREATAS function could work here. I would maybe need to see the model and try a few things if we can’t get a solution here.