How to create a matrix visualization with data from different tables (fields) with same column names

[red_arrowhead]

I have to create a matrix visulization with data from two different tables. I have column named Type in both the tables and 53 more columns for calander weeks with data for those Types. I have to create a visual as matrix with for all the types from both tables for all calander week. For example, in visual I have those calander weeks colums only once and data from both tables together in those calander weeks with respect to those types. I tries to create a relation but for one table data comes into 53 calander weeks columns and again new columns start with calander week 1 to 53 for second table. I want it together. These calander weeks coluns are calculated columns in power BI data tab so I cant use them in power query as well. How can I do it? 

Also, can I keep dymanic selection of these calander week columns in visual. For example, I want to select the weeks, data fro which will be displayed in the visual. 

Looking forward for hints to solve this issue.

[v-zhenbw-msft]

Hi @red_arrowhead ,

How about the result after you follow the suggestions mentioned in my original post?

If you've fixed the issue on your own please kindly share your solution. If the above posts help, please kindly mark it as a solution to help others find it more quickly.

Best regards,

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-zhenbw-msft]

Hi @red_arrowhead ,

You can unpivot in Data tab using DAX, please refer this thread.

https://stackoverflow.com/questions/50213905/is-it-possible-to-unpivot-in-power-bi-using-dax

But it would be a huge DAX code because you have 53 columns. You need to UNION 53 times.

We suggest you can create the CW column in Query Editor, not using DAX. Then you can use the unpivot function.

If it doesn’t meet your requirement, could you please provide a mockup sample based on fake data in a pbix file?

Maybe we can find a way to solve your issue using M query.

Please upload your files to OneDrive For Business and share the link here. Please don't contain any confidential information or real data in your reply.

Best regards,

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-zhenbw-msft]

Hi @red_arrowhead ,

Does your data structure like this?

And do you want to create a slicer using the first CW row?

If yes, you can refer the following steps.

1. Select Type column and unpivoted other columns.

2. Then we can create a matrix table like this,

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?

Best regards,

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

BTW, pbix as attached.

[red_arrowhead]

Thanks a lot for your reply. It explains the problem. But there are few different things in my case. I cannot access those CW columns in power query because, I have calculated those in Power BI Data Tab from other columns as below

So, as they are calculated here in data tab, when I go to transform data, I cant see these CW columns there. So how can I make a slicer using those columns? 

Also, CW 01 to CW 53 are present in two different sheets with there own dased on type and I want to merge those data based on cost type in visulization. For example, Data for Type A, B, C from sheet 1 for CW 1 and data for type D,E,F from sheet 2 for CW 1 should apper in single CW 1 column. But what currently happening is two different CW 1 are being created in visulization matrix, one for A,B,C data and another for D,E,F data. Similar for other 52 weeks as well. 

I want to combine them and want dynamic CW 01-53 slicer.

The format how I want the visulization is 

ith data for specific CW from both files together as per the type. And dynamic slicer using CW. 

As I am unable to use power query for those columns how can I proceed?

[v-zhenbw-msft]

Hi @red_arrowhead ,

What is the structure of your table? The first screenshot or second?

Could you please provide a mockup sample based on fake data or describe the fields of each tables and the relations between tables simply?

It will be helpful if you can show us the exact expected result based on the tables.

Please upload your files to OneDrive For Business and share the link here. Please don't contain any Confidential Information or Real data in your reply.

Best regards,

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[red_arrowhead]

The data I want to display in visulization is as below. 

There are data for all calander weeks which are calculated by formula in Power BI data tab. 

There are two different data sets similar to the screenshot above both having a column called  Type and corresponding data for all 53 calander weeks. 

Now I want to have a visulization table with this data specifflically for selected CW. I want this data to be merged into visulization. along with dymanic selection of which columns to dislpay. 

Currently the issue is:

This is the screenshot of the matrix visulzation I have created. I have columns from CW 01 to CW 53. But after popolating the data from first table, the data from new table starts populating by creating new column sets CW 01 to CW 53. I want the data for both tables  together. Also I used a slicer to select the CW to display, but I am not able to dynamically select the columns. 

How can I do these ?

[amitchandak]

@red_arrowhead , You need to create a common/ bridge table. and use that to analyze together

https://www.youtube.com/watch?v=Bkf35Roman8

also check

Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...

[red_arrowhead]

Thanks for the reply. As suggested I have created the common bridge table. I have used the 'Type ' column as the common bridge. But when I put data in matrix visulization data from table 1 is inputted in columns for CW 01 to CW 53 and for the next table it again inputs a new CW 01 to CW 53 columns. I want all the data together. I mean, CW 01 data for both tables should come in CW 01 column together for specific 'Types'. How can I proceed for that?