Forum Discussion

Ninja_Powered's avatar
Ninja_Powered
Frequent Visitor
3 years ago
Solved

Dimension Table with Two Columns

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...
Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    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's avatar
rubayatyasmin
Icon for Community Champion rankCommunity Champion
3 years ago

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's avatar
    Ninja_Powered
    Frequent Visitor
    3 years ago

    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's avatar
      rubayatyasmin
      Icon for Community Champion rankCommunity Champion
      3 years ago

      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.