Can two columns be in a Dimension table?

[Anonymous]

Hello, 

I am trying to set up a dimension table that can be used in a filter.   The filter I specifically would like to use is a hierarchy filter.   

The reason why is why have a "Bill to" customer....and then a "Ship to"  customer.     And the Bill to customer can have multiple Ship to  affiliates.     Or, the Bill to and Ship to could be the same customer.      (note that Affiliate 1, 2, is not the same company, just using it for my example).  

Example: 

Bill To                        Ship To

Company A               Affiliate 1

Company A               Affiliate 2 

Company B               Company B

Company C               Affiliate 1

Company C               Affiliate 2

Company C               Affiliate 3

Company C              Company C

So, per above, Company C could be both the Bill to and Ship to customer, but they could also be shipping to different "ship to" customers.  

Compa

[Anonymous]

I would create a separate dimensional table. 

create a new table with ship to as a distinct field. 

example: customer table = distinct(ship to) 

then add a column and look up the company name from the sales table and use this new table to create your hierarchy. 

[simrantuli]

Hi @Anonymous,

I don't quite understand your requirement. Could you please share some sample data?

Best Regards

Simran Tuli

[Anonymous]

@simrantuli     Normally a dimesnion table would have a unique list of, in this case, customers.   

I have a unique list of customers, Bill To, and another unique list  for Ship To.     Right now they live in two separate dimension tables.  

You can use two separate tables in the hiearchy filter visual.   

I am trying to see if there is a way to have both of these unique list of values in one dimension table. 

[Anonymous]

Sorry, I typed to fast.  

I meant to say you "cannot" use two tables in the hiearchy filter.  

If both of these fields exist in the same table, you can.    Which is why I want a dimension table, if possible, with both values so I can use that filter on  a page that contains multiple tables  (each of which contain the customer bill to or ship to name). 

[v-shex-msft]

Hi @Anonymous,

Yes, current you can't use multiple table fields to build a dimension(hierarchy) field. 

For your scenario, I'd like to suggest you add a value column on the table which has more records that other to lookup field value from other tables, then you can use raw table fields and calculated fields to build a hierarchy field that can be used in the slicer. (notice: build a relationship on this field to keep filter effect apply to other table based on the relationship )
Regards,

Xiaoxin Sheng

[Anonymous]

Hi Xiaoxin -  Would it be possible to create a super simple example of what you are describing in a pbix file that you could upload?  I'm not completely sure I follow what you are saying, but maybe a simple example would help.  

Thank you,

[v-shex-msft]

Hi @Anonymous,

Did you have any ID fields that exist in both two tables used to mapping two table records?

If this is a case, you can create a calculated table to extract id from two tables and add custom columns to lookup value from two tables and use these custom fields to create a hierarchy that you used in slicer to filter two table records. (relationships are based on the ID field and use bridge table to link other tables)

Bridge =
ADDCOLUMNS (
    DISTINCT ( UNION ( ALL ( 'Bill To'[ID] ), ALL ( 'Ship To'[ID] ) ) ),
    "Bill To", LOOKUPVALUE ( 'Bill To'[Bill To], 'Bill To'[ID], [ID] ),
    "Ship To", LOOKUPVALUE ( 'Ship To'[Ship To], 'Ship To'[ID], [ID] )
)

Regards,

Xiaoxin Sheng