Combining information without getting duplicate rows

[Lumegu]

Dear community,

I have a question and was wondering if this is even possible. I would like to combine the data from two queries (items_table and information_table) into one visual (desired output). If I create a simple normal table (and relating both queries) I end up with duplicate descriptions if an item has multiple pieces of information. 

I would like the desired output to only show the item once and depending on the amount of related information lines, one or more lines showing related information for this item. 

I now visualize in a table, but perhaps you have better suggestions how to display this best!

items_table:

Item	Description
1	Book
2	House
3	Door
4	Window

information_table

Information number	Related Item	Information
11	1	Blue
12	1	New
13	2	For sale
14	2	Large
15	3	Brown

Desired output: (e.g. in below example in the first 2 rows all information should be merged (no empty cells) and  item 1 should display 2 related pieces of information in separate cells)

Item	Description	Amount of related information	Related information
1	Book	2	11. Blue
			12. New
2	House	2	13. For sale
			14. Large
3	Door	1	15. Brown
4	Window	0

[Anonymous · Accepted solution]

Hi @Lumegu ,

Try to build your matrix by measure and transform it in Format. 

I build a sample by your data. I turn off Stepped layout/ Word wrap function in Format and then reduce the width of columns which I don't need to 0.

Then create measures as below.

Amount of related information = 
IF(ISINSCOPE(information_table[Information number]),BLANK(),COUNT(information_table[Information])+0)

Related information = 
IF(ISINSCOPE(information_table[Information number]), MAX(information_table[Information]),BLANK())

Related Information Number = IF(ISINSCOPE(information_table[Information number]), SUM(information_table[Information number]),BLANK())

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Lumegu , Matrix is nearest. But will not display as is what you want.

https://docs.microsoft.com/en-us/power-bi/visuals/desktop-matrix-visual

You can check for custom visual

https://appsource.microsoft.com/en-us/marketplace/apps?product=power-bi-visuals