Reply
Frequent Visitor
Posts: 5
Registered: ‎07-03-2018
Accepted Solution

Need to manage many to many relationship filters

 

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.

 

Many-Many Relationship.png

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

thanks


Accepted Solutions
Super User
Posts: 668
Registered: ‎11-01-2017

Re: Need to manage many to many relationship filters

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

View solution in original post


All Replies
Highlighted
Super User
Posts: 668
Registered: ‎11-01-2017

Re: Need to manage many to many relationship filters

@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

Frequent Visitor
Posts: 5
Registered: ‎07-03-2018

Re: Need to manage many to many relationship filters

[ Edited ]

@rajendran

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

Super User
Posts: 668
Registered: ‎11-01-2017

Re: Need to manage many to many relationship filters

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