Dimension Table with Two Columns

[Ninja_Powered]

I have created a dimension table in a Power BI report to allow me to filter suppliers from multiple tables as below. However, it's now been highlighted to me that the two suppliers with the same name could have a different supplier code. It is possible to create a dimension table with both the supplier name and the supplier number?

Supplier Dimension = 
DISTINCT(
        UNION(
                ALLNOBLANKROW(Table1[Supplier Name]),
                ALLNOBLANKROW(Table2[Supplier Name]),
                ALLNOBLANKROW(Table3[Supplier Name])
        ))

[Anonymous · Accepted solution]

Hi @Ninja_Powered ,

Please try like:

Table = 
UNION(
    ALLNOBLANKROW('Table1'[Supplier Name],'Table1'[Supplier code]),
    ALLNOBLANKROW('Table2'[Supplier Name],'Table2'[Supplier code]),
    ALLNOBLANKROW('Table3'[Supplier Name],'Table3'[Supplier code])
)

Please check the demo file.

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[rubayatyasmin]

Hi, @Ninja_Powered 

Assuming you have supplier number column in your table, as the numbers would be different so there should be a supplier type , modify the DAX like 

Supplier Dimension = 
UNION(
    ADDCOLUMNS(ALLNOBLANKROW(Table1), "Supplier Name", Table1[Supplier Name], "Supplier Number", Table1[Supplier Number], "SupplierType", "Table1"),
    ADDCOLUMNS(ALLNOBLANKROW(Table2), "Supplier Name", Table2[Supplier Name], "Supplier Number", Table2[Supplier Number], "SupplierType", "Table2"),
    ADDCOLUMNS(ALLNOBLANKROW(Table3), "Supplier Name", Table3[Supplier Name], "Supplier Number", Table3[Supplier Number], "SupplierType", "Table3")
)

this is merely an idea, this should help you achieve your goal. 

[Ninja_Powered]

@rubayatyasmin Can I use the AddColumns function in this way? I am getting the error 'Function 'ADDCOLUMNS' cannot add column [Supplier Number] since it already exists.'

[rubayatyasmin]

alright, then just add the column instead of adding new column and use the supplier number. Like, 

Supplier Dimension = 
        UNION(
                ALLNOBLANKROW(Table1[Supplier Name], Table1[Supplier Number]),.......//assuming you have supplier number or similar column in your table.