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

[Anonymous]

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 Name	2018	2019	2020
aaaaaaaaaaaaaaaaaaaaaaaaaa1	xxx	xxx	xxx
aaaaaaaaaaaaaaaaaaaaaaaaaa2	xxx	xxx	xxx
aaaaaaaaaaaaaaaaaaaaaaaaaa3	xxx	xxx	xxx

Customer Sales by Product (Matrix A)

Customer Name	Product A	Product B	Product C
aaaaaaaaaaaaaaaaaaaaaaaaaa1	xxx	xxx	xxx
aaaaaaaaaaaaaaaaaaaaaaaaaa2	xxx	xxx	xxx
aaaaaaaaaaaaaaaaaaaaaaaaaa3	xxx	xxx	xxx

Customer Sales by Year & Product (Combined Matrix)

Customer Name	2018	2019	2020	Product A	Product B	Product C
aaaaaaaaaaaaaaaaaaaaaaaaaa1	xxx	xxx	xxx	xxx	xxx	xxx
aaaaaaaaaaaaaaaaaaaaaaaaaa2	xxx	xxx	xxx	xxx	xxx	xxx
aaaaaaaaaaaaaaaaaaaaaaaaaa3	xxx	xxx	xxx	xxx	xxx	xxx

[v-diye-msft]

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:

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 

[Anonymous]

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 Name	Year	Product	Sales
C1	2019	P1	10
C1	2019	P2	20
C1	2020	P1	31
C1	2020	P2	42

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

Customer Name	2019	2020	P1	P2
C1	30	73	41	62

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.

[v-diye-msft · Accepted solution]

Hi @Anonymous 

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

[Anonymous]

I have gotten that impression as well. Thanks anyhow! 

[Greg_Deckler]

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

[Anonymous]

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!