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

[Anonymous]

I am trying to create a matrix like this below:

(and with a filter for geograpy) 

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 

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.)

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! 

[MariaP]

Hi @Anonymous ,

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 Results

Final Result

Hope this helps,

Maria

[Anonymous]

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

Thank you for that @Anonymous ,

@Stachu solution is simpler so better for you ! 

Cheers,
Maria

[Stachu · Accepted solution]

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