Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 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?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.