How do I create the relationships

[Anonymous]

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?

[mahoneypat]

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]

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 · Accepted solution]

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.