Forum Discussion

Ninja_Powered's avatar
Ninja_Powered
Frequent Visitor
2 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 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])
        ))

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    2 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

4 Replies

  • rubayatyasmin's avatar
    rubayatyasmin
    Community Champion
    2 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
      2 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
        Community Champion
        2 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.

         

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 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