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...
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.

mahoneypat's avatar
mahoneypat
Icon for Microsoft Employee rankMicrosoft 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
      Icon for Community Support rankCommunity 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.