cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sadhikari707 Frequent Visitor
Frequent Visitor

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

1 ACCEPTED SOLUTION

Accepted Solutions
rajendran Super Contributor
Super Contributor

Re: Need to manage many to many relationship filters

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

3 REPLIES 3
Highlighted
rajendran Super Contributor
Super Contributor

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

sadhikari707 Frequent Visitor
Frequent Visitor

Re: Need to manage many to many relationship filters

@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

rajendran Super Contributor
Super Contributor

Re: Need to manage many to many relationship filters

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

Helpful resources

Announcements
Community Highlights

Community Highlights

Find out what's new in the Power BI Community!

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 367 members 3,851 guests
Please welcome our newest community members: