cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper V
Helper V

Measures 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 EmpMonth0Measure0  
E1 E120180110  
E2 E220180220  
E3 E320180315  
  Fact2    
  EmpMonth2Measure2 
  E120180110  
  E220180220  
  E320180315  
       
  Fact3    
  EmpMonth3Measure3Month4Measure4
  E12018011020180510
  E22018022020180120
  E32018031520180715
2 REPLIES 2
Super User IV
Super User IV

Re: Measures from Multiple Facts with different dimensions

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.




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

Highlighted
Helper V
Helper V

Re: Measures from Multiple Facts with different dimensions

@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?

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Super Users of the Quarter - Q2 2020

Super Users of the Quarter - Q2 2020

Who are our Super User Superstars? Who made it to the top of the leaderboards? Get the answers!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Community Summit Australia – Join Online!

Community Summit Australia – Join Online!

Be a part of the leading Microsoft Business Applications digital event, curated for the APAC community.

Top Solution Authors
Top Kudoed Authors