Isnt it possible to have a common data table?

[Anonymous]

Hello fellow PowerBi-users,

I struggle with the following thing:

I got one Table with the connections of Customers and Suppliers, but they are in 2 different columns.

Right now I keep 2 separate tables for the description of the Customers and suppliers but I would like to have them in the same table as "common data". But then I always need 2 slicers in the report if I want to filter and search for a description (e.g. "nice").

And PowerBi doesnt let me have 2 active relationships from one column of a table to 2 different columns in another table. Does anybody know a workaround or another way to do it or is it straight impossible?

For reference here are example data sets:

table1

Customer	Description
CustomerA	C Buys F alot
CustomerB	C Buys G alot
CustomerC	C Buys H alot
CustomerD	C Buys I alot
CustomerE	C Buys J alot
CustomerF	C Buys K alot
CustomerG	C Buys L alot

table2

Supplier	Description
Supplier1	Su is nice
Supplier2	Su is ok
Supplier3	Su is best
Supplier4	Su returns a lot

table3

Supplier	Customer	Delivery	Amount
Supplier1	CustomerA	X	10
Supplier1	CustomerB	X	15
Supplier1	CustomerC	X	10
CustomerC	CustomerD	Y	5
CustomerC	CustomerE	Y	5
Supplier2	CustomerF	J	5
CustomerA	CustomerG	V1	10
CustomerB	CustomerG	V2	10
CustomerC	CustomerG	V3	10
CustomerD	CustomerG	V4	15
CustomerE	CustomerG	V5	15
CustomerF	CustomerG	V6	15

So isnt it possible to combine table1 and table 2 into one so i dont need to have 2 datasets (and 2 slicers where i need to search for the description)?

Thank you a lot for any help!

Best Regards,

Strathos

[rbriga · Accepted solution]

You can then use Alberto Ferrari's technique to create an "OR" slicer, so the result would filter on either slicer rather than both.

Another option is to create a "Description" dimension table which would be a distinct union of the descriptions from Suppliers and Customers, and create relationship to that table. Than, use the field from this union table as your filter.

You need to see if this creatues a disambiguity, though.

[rbriga]

The way I see this, Table 1 is your Customer dimension table, Table 2 is your Supplier dimension table, and TAble 3 is your delivery fact table.

These are 2 relationships (Customers[Customer] ->Delivery[Customer], Suppliers[Supplier] ->Delivery[Supplier]).

Any slicing shouldbe made on the dimension table fields, not the fact table.

For example, a slicer on Suppliers[Description] to search for "nice".

What is it you can't accomplish with this setup?

[Anonymous]

Hi rbriga,

thanks a lot for the fast reply:)

you captured it perfectly, but maybe I formulated it wrong. I do exactly that, but then i need a slicer for Suppliers[Description] anda second one for Customer[Description] if i search for a sepcific description (because i dont know where it is/to which it applies)