Forum Discussion
Isnt it possible to have a common data table?
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
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.
3 Replies
- rbriga
Impactful Individual
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?
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)
- rbriga
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.