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
Anonymous
Not applicable

Combine two tables/matrices into one (with Live Connection)

Hi everyone,

 

I want to use matrices to display customer sales by year and by product categories. The problem is that my customer names are quite long, so using two different tables takes up a lot of real estate in my report. Furthermore, it's hard to compare the numbers side-by-side since the user has to scroll in both tables and since word wrapping results in differences in row height.

The best alternative would by to combine the two tables into the same table/matrix (see example below). I have, however, not found a solution that produces the desired effect. Does anyone know a way to achieve this? Maybe through a custom visual?

OBS: I am working with a live connection to a database and can't make changes to the data model, aside from adding measures.

 

Customer Sales by Year (Matrix A)

Customer Name201820192020
aaaaaaaaaaaaaaaaaaaaaaaaaa1xxxxxxxxx
aaaaaaaaaaaaaaaaaaaaaaaaaa2xxxxxxxxx
aaaaaaaaaaaaaaaaaaaaaaaaaa3xxxxxxxxx

 

Customer Sales by Product (Matrix A)

Customer NameProduct AProduct BProduct C
aaaaaaaaaaaaaaaaaaaaaaaaaa1xxxxxxxxx
aaaaaaaaaaaaaaaaaaaaaaaaaa2xxxxxxxxx
aaaaaaaaaaaaaaaaaaaaaaaaaa3xxxxxxxxx

 

Customer Sales by Year & Product (Combined Matrix)

Customer Name201820192020Product AProduct BProduct C
aaaaaaaaaaaaaaaaaaaaaaaaaa1xxxxxxxxxxxxxxxxxx
aaaaaaaaaaaaaaaaaaaaaaaaaa2xxxxxxxxxxxxxxxxxx
aaaaaaaaaaaaaaaaaaaaaaaaaa3xxxxxxxxxxxxxxxxxx

 

1 ACCEPTED SOLUTION

Hi @Anonymous 

 

Sorry, I don't think it's accessible under live-connection.

 

 

Community Support Team _ Dina Ye
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
v-diye-msft
Community Support
Community Support

Hi @Anonymous 

 

1. If the live-connection is caused by connecting the dataset from power bi service, you can just download the pbix and manage the relationship in desktop like below:

02.PNG03.PNG

2. You can also use the LOOKUPVALUE function by DAX to create a measure as value:

https://docs.microsoft.com/en-us/dax/lookupvalue-function-dax 

04.PNG

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.
Anonymous
Not applicable

Hi Dina, 

Thanks for your response! Unfortunately, I'm working with a live connection to a SQL analysis service (the underlying model is tabular). So I can't change anything in the data model; relationships, columns, tables, etc. To clarify, both product and year are already connected to customer name. Simplified, the data looks something like this:

 

Customer NameYearProductSales
C12019P110
C12019P220
C12020P131
C12020P242

 

And I want to have a visual that displays the data like this:

 

Customer Name20192020P1P2
C130734162

 

So the problem is how I could show Sales (value) by Customer names (rows), using two different dimensions as column headers at the same time (Year & Product). Backend, everything is already connected.

Hi @Anonymous 

 

Sorry, I don't think it's accessible under live-connection.

 

 

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.
Anonymous
Not applicable

I have gotten that impression as well. Thanks anyhow! 

Greg_Deckler
Super User
Super User

See if The New Hotness helps, it was specifically designed to support custom matrix columns. https://community.powerbi.com/t5/Quick-Measures-Gallery/The-New-Hotness-Custom-Matrix-Hierarchy/td-p...


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

Thanks Greg! 🙂 If I compromise on a few things this solution may work. But I would prefer not to hardcode the product columns since there are a lot of them, they tend to change, and I want them to change dynamically depending on the customer selection (the same goes for year). Furthermore, I would like to preserve the product hierarchy used for drilldown. I'll try your solution out; if it turns out alright, I'll make sure to mark the post as resolved.

PS: I hadn't seen this trick before. If it doesn't work for this problem, it sure does reduce the headache associated with some other tables. Thanks!

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