Forum Discussion

Lumegu's avatar
Lumegu
Frequent Visitor
4 years ago
Solved

Combining information without getting duplicate rows

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:

ItemDescription
1Book
2House
3Door
4Window

 

information_table

Information numberRelated ItemInformation
111Blue
121New
132For sale
142Large
153Brown

 

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)

 

ItemDescriptionAmount of related informationRelated information
1Book211. Blue
   12. New
2House213. For sale
   14. Large
3Door115. Brown
4Window0 
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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.

     

     

2 Replies