Measures from Multiple Facts with different dimensions

[deepu299]

Hi, I have a model with multiple facts and a dimension (hierarchy). My requirement is to build a matrix report with all these measures and be able to use the slicers from Hierarchy table. 

Something like this - Employees are common in all the tables and the relations are based on Emp. I want the matrix to be by Month, but since the Month columns are different for different measures, I am having difficuty in building this. Measure0 should be based on Month0, Measure2 by Month2 and Mesure3 and 4 by Month 3 and 4 respectively. I have tried to build a table using DAX, but then I am not able to bring in Employees over there due to which slicers doesn't interact. 

Can anyone please suggest me a workaround? Thanks in advance.

Hierarchy		Fact1
Emp		Emp	Month0	Measure0
E1		E1	201801	10
E2		E2	201802	20
E3		E3	201803	15
		Fact2
		Emp	Month2	Measure2
		E1	201801	10
		E2	201802	20
		E3	201803	15
		Fact3
		Emp	Month3	Measure3	Month4	Measure4
		E1	201801	10	201805	10
		E2	201802	20	201801	20
		E3	201803	15	201807	15

[GilbertQ]

Hi there

What I would suggest doing is to merge your 3 fact tables into one table.

Then to create a date table, and create a relationship from your fact table to your date table.
Then also create a relationship from your Emp table to your Fact table.

Once the above is done and your data model is created as a star schema you will then be able to create the matrix as required.

[deepu299]

@GilbertQ Thanks for the response. I have tried merging, it didn't work ideally due to other dimensions in those tables. They are all at different levels and while merging I have to do an aggregation. My final measure will be an Average in the matrix table. So while merging if I do SUM, it will add up all 0's due to which avegrage impacts. If I do an Avg while merging, the final table will do Average of Averages when seen by month. Is there any reporting workaround other than merging, can you please suggest?