Need to manage many to many relationship filters

[sadhikari707]

Hi  All,

Need help in managing the  M-M filters  for the tables mentioned below.

Orderdetails table hold orders at item level  and orderlog table  holds the data at order level.

whenever there is a change in shipdate of  order  a new entery is logged into orderlog table so one order can have multiple enteries.

Orderdetails and Orderlog table have M-M relationship , I created a bridge table that will hold all the Order# and have  relationships setup like this

BridgeOrderHeader[Order#] -->OrderDetails[Order#] (1:M)

BridgeOrderHeader[Order#] -->OrderLog[Order#] (1:M)

Measures created 

 *OrderDetails -- Quantity

 *OrderLog  -- LogCount

Intent is to select Order# from Orderdetails table and if histroy needed from orderlog table then just pull the data from  orderlog table and  bridgetable should carry the filters from  OrderDetails to OrderLog

Whats happening at the moment

     **If Order# is pulled from orderdetail and any field from orderlog table it does not give any results and throw an error that                no relationship between 2 tables

    **If Order# is pulled from orderdetail and then LogCount from orderlog  then it works

    ** If Order# is pulled and then logcount and then any fields from orderlog then also it works.

Please let me know how to handle this situation  or if i am missing anything in setup.

thanks

[Anonymous]

@sadhikari707

Your cross filter direction is from Bridge table to orderdetail and order log table.

So, if you pull the order# from the bridge table, you should be getting the correct result.

Thanks
Raj

[sadhikari707]

@Anonymous

Yes i tried that and its working .

the only issues is that  i had that table hidden and wanted the use the order# from  orderdetails table  so that we don't have too many tables in the model.

If we go by what you explained and what i have tested too , it means that we have to have  bridge table visible ?

if there any other approach we can take to behave like what i am thinking ?

thanks

[Anonymous · Accepted solution]

Yes, we need bridge table as you need to pull the order# from that table.