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, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
hcknbrgr
Frequent Visitor

Merging two Matrix tables into a single Matrix table

Hi all, fairly new to Power BI and DAX.

I have created two Matrix tables. Matrix Table 1 uses Product in the Columns section to split out the counts of product purchases.  Table 2 has no Columns field and just has the sum of purchases.  Both tables have a filter to only include records with a status of New.  I would like to create a single table to display that has the results of both tables. 

Here is my data and the table I want to create

Data TableData TableTables.png

If I include the Amount column in my table1 values, I get the following, which is not the desired result.

Table4.png

 

I could create Measures for Product A, Product B, etc., to create the table, but then whenever a new product is created a new measure would need to be created.  It seems like there ought to be a way to create this more dynamically, hopefully soemone can point the way.

 

Thank you,

Greg

1 ACCEPTED SOLUTION
v-yulgu-msft
Microsoft Employee
Microsoft Employee

Hi @hcknbrgr,

 

It is not available to achieve that. By default, we add a column/measure into value section in a matrix visual, it will be grouped automatically. As it shows in above image, the measure/column will show for each group instance.

 

Best regards,
Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

6 REPLIES 6
hcknbrgr
Frequent Visitor

In the end I went with creating a new calculated column for each product and built the table with fixed columns.  When I created the Dax table my data was now split into two tables and filters on other fields not included in the sample were not applied to the DAX table, so it was easier to keep everything in one table.

Hi 

I am trying to do the same ... 

I am not sure how you did manage to solve this...

I wonder if you could share a bit more details on how you did it...

 

I have 2 tables ... (data is coming from 2 data sets linked)

One where I have

CUSTOMER NAME / FIRST ACTIVITY / ACTIVITY TYPE / LAST ACTIVITY / ACTIVITY TYPE

 

and a second matrix visualization

CUSTOMER NAME / ACTIVITY TYPE 1 / ACTIVITY TYPE 2 etc ... and count of activity type as value.

 

I was trying to have a combined visualization that shows

 

CUSTOMER NAME FIRST ACTIVITY / ACTIVITY TYPE / LAST ACTIVITY / ACTIVITY TYPE and then the matrix part with the count of activity by type.

hcknbrgr
Frequent Visitor

I was hoping that I could create a DAX statement that would store the values of Table 1 and then I could pull that table and the Sum of Amount from my main data table.  But I am not there yet in my understanding of DAX.

I was able to find a DAX statement that I could modify and get the desired result by hard coding each Product, but again, this needs to be updated whenever there is a new Product introduced:

Table5.png

Hi @hcknbrgr,

 

You could submit this requirement as an idea at this page:How can we improve Power BI?  If it mentioned by many users, our product team might consider adding this feature in later release.

 

Best regards,
Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
v-yulgu-msft
Microsoft Employee
Microsoft Employee

Hi @hcknbrgr,

 

It is not available to achieve that. By default, we add a column/measure into value section in a matrix visual, it will be grouped automatically. As it shows in above image, the measure/column will show for each group instance.

 

Best regards,
Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Imagine how awesome it would be if you were able to hide all values of a measure in a table/matrix for still show the total.

 

I believe this feature is coming to the new table/matrix visuals, but is't not there yet.

/sdjensen

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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