Forum Discussion
Advocate VMeasures from Multiple Facts with different dimensions
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 |
2 Replies
- GilbertQ
Super User
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?
