Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
red_arrowhead
Helper I
Helper I

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

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.

8 REPLIES 8
v-zhenbw-msft
Community Support
Community Support

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
Community Support
Community Support

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
Community Support
Community Support

Hi @red_arrowhead ,

 

Does your data structure like this?

 

h1.jpg

 

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.

 

h2.jpg

 

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

 

h3.jpg

 

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.

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

red_arrowhead_0-1607439201913.png

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 

red_arrowhead_1-1607439684746.png

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
Community Support
Community Support

Hi @red_arrowhead ,

 

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

 

H1.jpg

 

H22.jpg

 

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.

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

red_arrowhead_0-1607331588750.png

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:

red_arrowhead_1-1607332145428.png

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
Super User
Super User

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

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

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?

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.