Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

How to setup multiple columns for visualization

Hi all,   I have been wrestling with an issue that was partially addressed by this forum but I can't quite get to a final solution. I am hoping the explanation below will help paint a picture of wh...
Need Help
  • Anonymous's avatar
    Anonymous
    8 years ago

    Hi all,

     

    I inspired myself from another post and found my solution :)

     

    Manufacturer = 
DISTINCT (
UNION (
       SELECTCOLUMNS('Current Inventory',"Manufacturer",'Current Inventory'[Manufacturer #1]),
       SELECTCOLUMNS('Current Inventory',"Manufacturer",'Current Inventory'[Manufacturer #2]),
       SELECTCOLUMNS('Current Inventory',"Manufacturer",'Current Inventory'[Manufacturer #3]),
       SELECTCOLUMNS('Current Inventory',"Manufacturer",'Current Inventory'[Manufacturer #4]),
       SELECTCOLUMNS('Current Inventory',"Manufacturer",'Current Inventory'[Manufacturer #5]),
       SELECTCOLUMNS('Current Inventory',"Manufacturer",'Current Inventory'[Manufacturer #6]),
       SELECTCOLUMNS('Current Inventory',"Manufacturer",'Current Inventory'[Manufacturer #7]),
       SELECTCOLUMNS('Current Inventory',"Manufacturer",'Current Inventory'[Manufacturer #8]),
       SELECTCOLUMNS('Current Inventory',"Manufacturer",'Current Inventory'[Manufacturer #9]),
       SELECTCOLUMNS('Current Inventory',"Manufacturer",'Current Inventory'[Manufacturer #10]),
       SELECTCOLUMNS('Current Inventory',"Manufacturer",'Current Inventory'[Manufacturer #11])
)
)
Anonymous's avatar
Anonymous
Not applicable
8 years ago

Hello,

 

Below is an example of what my table would look like (with a lot more information in the middle). Important to note here that there are no duplicates in the STOCK NUMBER column as it is used on a 1 to many relationship

 

STOCK NUMBERManufacturer 1Manufacturer 2Manufacturer 3Manufacturer 4
700000007VolvoBMW  
700000010FordToyotaVolvo 
700000230VWAlpha RomeoHondaBMW
700000440HondaToyotaLexusVolvo
700000560Hyundai   

 

Below is an example of what visual I am trying to achieve

 

 

Effectively list all the manufacturers (without duplicates) across all tables with a count of stock numbers they are listed against.

 

Does anyone have a clever suggestion?

 

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Hi all,

 

I inspired myself from another post and found my solution :)

 

Manufacturer = 
DISTINCT (
UNION (
       SELECTCOLUMNS('Current Inventory',"Manufacturer",'Current Inventory'[Manufacturer #1]),
       SELECTCOLUMNS('Current Inventory',"Manufacturer",'Current Inventory'[Manufacturer #2]),
       SELECTCOLUMNS('Current Inventory',"Manufacturer",'Current Inventory'[Manufacturer #3]),
       SELECTCOLUMNS('Current Inventory',"Manufacturer",'Current Inventory'[Manufacturer #4]),
       SELECTCOLUMNS('Current Inventory',"Manufacturer",'Current Inventory'[Manufacturer #5]),
       SELECTCOLUMNS('Current Inventory',"Manufacturer",'Current Inventory'[Manufacturer #6]),
       SELECTCOLUMNS('Current Inventory',"Manufacturer",'Current Inventory'[Manufacturer #7]),
       SELECTCOLUMNS('Current Inventory',"Manufacturer",'Current Inventory'[Manufacturer #8]),
       SELECTCOLUMNS('Current Inventory',"Manufacturer",'Current Inventory'[Manufacturer #9]),
       SELECTCOLUMNS('Current Inventory',"Manufacturer",'Current Inventory'[Manufacturer #10]),
       SELECTCOLUMNS('Current Inventory',"Manufacturer",'Current Inventory'[Manufacturer #11])
)
)
  • thinkerbee5's avatar
    thinkerbee5
    Regular Visitor
    7 years ago

    Hello!  I am trying to do something very similar but am having trouble applying your solution.  can you please tell me what the "Manufacturer" piece refers to in your example?

     

    The error I'm getting states: "a table of multiple values was supplied where a single value was expected"

     

    Manufacturer =
    DISTINCT (
    UNION (
           SELECTCOLUMNS('Current Inventory',"Manufacturer",'Current Inventory'[Manufacturer #1]),
           SELECTCOLUMNS('Current Inventory',"Manufacturer",'Current Inventory'[Manufacturer #2]),
           SELECTCOLUMNS('Current Inventory',"Manufacturer",'Current Inventory'[Manufacturer #3]),
           SELECTCOLUMNS('Current Inventory',"Manufacturer",'Current Inventory'[Manufacturer #4]),
           SELECTCOLUMNS('Current Inventory',"Manufacturer",'Current Inventory'[Manufacturer #5]),
           SELECTCOLUMNS('Current Inventory',"Manufacturer",'Current Inventory'[Manufacturer #6]),
           SELECTCOLUMNS('Current Inventory',"Manufacturer",'Current Inventory'[Manufacturer #7]),
           SELECTCOLUMNS('Current Inventory',"Manufacturer",'Current Inventory'[Manufacturer #8]),
           SELECTCOLUMNS('Current Inventory',"Manufacturer",'Current Inventory'[Manufacturer #9]),
           SELECTCOLUMNS('Current Inventory',"Manufacturer",'Current Inventory'[Manufacturer #10]),
           SELECTCOLUMNS('Current Inventory',"Manufacturer",'Current Inventory'[Manufacturer #11])
    )
    )