How to setup multiple columns for visualization

[Anonymous]

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 what I'm trying to achieve and hopefully someone much more clever than me will have a suggestion 🙂

I have a pbi file with multiple tables linked to Oracle, two of which are of particular interest:

. one table 'Transactions' which pulls all purchase transactions (thousand of lines of purchase orders) with a column containing a stock number [STOCK NUMBER]. THis stock number appears multiple times in the table

. one table 'Current Inventory' which pulls all distinct stock numbers along with various data such as drawing number, manufacturer details, etc. I have 4 columns of interest [Manufacturer #1], [Manufacturer #2], [Manufacturer #3] and [Manufacturer #4]. This is because one part can theoritically be manufactured by 4 different companies (whilst bought from only one).

I have already concatenated the 4 manufacturer columns so I can have a text filter to display all purchases relating to items falling under a specific manufacturer.

Now here is what I'd like to do: have a graph (ie pie chart) which shows me who are the manufacturers that are being the stock numbers we purchase the most. Problem is, stock number 1 could have vendor A listed in [Manufacturer #1], stock number 2 could have nothing, stock number 3 could have Vendor A listed in Manufacturer #1 as well as Manufacturer #2, etc etc.

I thought of creating a new table with two columns, one listing stock numbers (duplicated) and the other the manufacturers (being a combination of all 4 fields above). I'm not sure how to do this and then wonder what relationship to create to be able to do all this filtering.

Does anyone have a clever idea?

Thanks in advance 🙂

OF

[v-yulgu-msft]

Hi @Anonymous,

Please illustrate your scenario with sample data and image of desired output to help us better understand.

How to Get Your Question Answered Quickly

Regards,

Yuliana Gu

[Anonymous]

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 NUMBER	Manufacturer 1	Manufacturer 2	Manufacturer 3	Manufacturer 4
700000007	Volvo	BMW
700000010	Ford	Toyota	Volvo
700000230	VW	Alpha Romeo	Honda	BMW
700000440	Honda	Toyota	Lexus	Volvo
700000560	Hyundai

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 · Accepted solution]

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]

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])
)
)