cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rlee1982
Helper III
Helper III

How to put two measures which have different keys in one matrix?

I am trying to create a matrix like this below:

(and with a filter for geograpy) 

 

matrix_A.JPG

 

filter.JPG

 

However, measure 1 (from table 1) and measure 2 (from table 2) have different keys (Badge ID vs. Member ID) and have no relationship to each other. What only in common is both of them have "Year" column and "Geo" column 

 

table1.JPG

 

table2.JPG

 

Now, if I would like to keep column headers, i.e. 2012, 2013, 2014, all the time. (that means the blank values will be shown in the matrix) and ensure the geo filter works well and apply to both measure 1 and measure 2. How should I do for measure 2?

 

 I tried to add two index tables but it seems Power BI does not allow many-to-many relationships exist between those four tables. (i.e. table 1 and 2 both have many-to-many relationships with index Table 1 and 2.)

 

index.JPG

Is there any way I can make the matrix happen?

 

(well, I even tried to create two seperate charts and made the second chart's column header white to phyically merge two tables into one.)

 

Thank you in advance! 

 

 

 

 

1 ACCEPTED SOLUTION
Stachu
Community Champion
Community Champion

if you set the relationships with your index tables as single direction 1:many with both table 1 and table 2, and then use the Geo and Year from the index tables in the matrix then both the measures should work fine



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Proud to be a Super User!

View solution in original post

4 REPLIES 4
MariaP
Solution Supplier
Solution Supplier

Hi @rlee1982 ,

 

I have created a list then a table from your geo field. I have merged your 2 tables into one table and unpivottoed as shown in the attached file. This produces the following results as I have understood from your question. It also allows you to join your geo field and filter as outlined.

The main trick is to unpivot the data as shown in the merged columns.

 

Power BI file is here: SAMPLE PBIX FILE

 

 

Unpivotted ResultsUnpivotted Results

Final ResultFinal Result

Hope this helps,

Maria

@MariaP thank you for your reply. It also works well. Feel sorry that I couldn't select your answer as the best solution too!

MariaP
Solution Supplier
Solution Supplier

Thank you for that @rlee1982 ,

 

@Stachu solution is simpler so better for you ! Smiley Happy

 

Cheers,
Maria

Stachu
Community Champion
Community Champion

if you set the relationships with your index tables as single direction 1:many with both table 1 and table 2, and then use the Geo and Year from the index tables in the matrix then both the measures should work fine



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Proud to be a Super User!

View solution in original post

Helpful resources

Announcements
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors
Top Kudoed Authors