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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. 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 ?

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors