Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

How do I create the relationships

Hi,

 

I am new to PowerBi and currently stuck on the relationship between the tables below.

 

Orders

 

Stock Allocation

 

Power Bi Desktop

 

How to create the relationship to allow the slicer filter on both tables by branch?

 

 

 

Need Help
  • v-xiaotang's avatar
    v-xiaotang
    4 years ago

    Hi Anonymous 

    You can try this,

    (1) Create the auxiliary tables

    Table = DISTINCT( SELECTCOLUMNS(Orders,"Sales Date",Orders[Sales Date],"Order",Orders[Order]))

    (2) create the measures below,

    Qty = 
CALCULATE (
    SUM ( Orders[Qty] ),
    FILTER (
        ALL ( Orders ),
        Orders[Branch] = SELECTEDVALUE ( 'Branch Filter'[Branch] )
            && Orders[Sales Date] = MIN ( 'Table'[Sales Date] )
            && Orders[Order] = MIN ( 'Table'[Order] )
    )
)
    Allocation = 
CALCULATE (
    SUM ( 'Stock Allocation'[Qty] ),
    FILTER (
        ALL ( 'Stock Allocation' ),
        'Stock Allocation'[Branch] = SELECTEDVALUE ( 'Branch Filter'[Branch] )
            && 'Stock Allocation'[Sales Date] = MIN ( 'Table'[Sales Date] )
            && 'Stock Allocation'[Order] = MIN ( 'Table'[Order] )
    )
)

    result

    Best Regards,

    Community Support Team _Tang

    If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

3 Replies

  • mahoneypat's avatar
    mahoneypat
    Microsoft Employee
    4 years ago

    Create another table with just the Branch values, either in the query editor by referencing one of your existing tables, or with a DAX table with DISTINCT(Orders[Branch]) for example. You can then relate the new table to both columns on the Branch columns.

     

    Pat

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi Pat,

       

      Thanks for your reply. I have tried this method before, but I couldn't get the correct number on allocation Qty.

       

       

       

      • v-xiaotang's avatar
        v-xiaotang
        Community Support
        4 years ago

        Hi Anonymous 

        You can try this,

        (1) Create the auxiliary tables

        Table = DISTINCT( SELECTCOLUMNS(Orders,"Sales Date",Orders[Sales Date],"Order",Orders[Order]))

        (2) create the measures below,

        Qty = 
CALCULATE (
    SUM ( Orders[Qty] ),
    FILTER (
        ALL ( Orders ),
        Orders[Branch] = SELECTEDVALUE ( 'Branch Filter'[Branch] )
            && Orders[Sales Date] = MIN ( 'Table'[Sales Date] )
            && Orders[Order] = MIN ( 'Table'[Order] )
    )
)
        Allocation = 
CALCULATE (
    SUM ( 'Stock Allocation'[Qty] ),
    FILTER (
        ALL ( 'Stock Allocation' ),
        'Stock Allocation'[Branch] = SELECTEDVALUE ( 'Branch Filter'[Branch] )
            && 'Stock Allocation'[Sales Date] = MIN ( 'Table'[Sales Date] )
            && 'Stock Allocation'[Order] = MIN ( 'Table'[Order] )
    )
)

        result

        Best Regards,

        Community Support Team _Tang

        If this post helps, please consider Accept it as the solution to help the other members find it more quickly.