none
why cant i see a many-to-many relationship option? RRS feed

  • Question

  • I have VS 2017, and sql server 2017. 

    According to this article, starting with sqlserver 2016+, bidirectional filtering was introduced to eliminate DAX queries need for many-to-many relationships to be created.

    i set the filter directions to both tables on my tabular project

    enter image description here

    as you can see, a many-to-many relationship still doesnt show up.

    we have a requirement for many to many but why cant i see it even though i have sql server 2017 and VS 2017??

    Note: the filter direction MUST be set to something. theres no none option, it either sets to the left table only or to both tables. and both of these options have the same 3 cardinality options, and no many to many option. 

    Tuesday, September 10, 2019 9:52 PM

Answers

  • In Most of the Modelling tools having tabular services (i.e., Power BI/ Azure Analysis Services/ SQL Server Analysis Services Tabular Project) doesn't support Many-to-Many relationships between the tables.

    More over Filter direction (To Both Tables) doesn't mean that it supports Many-to-Many relationship as it mean to filter data as in cascading way. (i.e., When you apply a filter on one table other table data will also be auto filtered and vice versa).

    To get rid of this limitation as "Many to Many" Relationship create an intermediate table as a Bridge table/Cross reference table which creates 3 tables overall maintaining One (One to Many) and second (Many to One).

    Hope this is helpful !!

    Thank you


    If this post helps to resolve your issue, please click the "Mark as Answer" of that post and/or click Answered "Vote as helpful" button of that post. By marking a post as Answered and/or Helpful, you help others find the answer faster.

    • Proposed as answer by Naveen Thummalapenta Tuesday, October 22, 2019 3:29 AM
    • Marked as answer by cataster Thursday, January 23, 2020 4:11 PM
    Friday, September 20, 2019 9:49 AM

All replies

  • In Most of the Modelling tools having tabular services (i.e., Power BI/ Azure Analysis Services/ SQL Server Analysis Services Tabular Project) doesn't support Many-to-Many relationships between the tables.

    More over Filter direction (To Both Tables) doesn't mean that it supports Many-to-Many relationship as it mean to filter data as in cascading way. (i.e., When you apply a filter on one table other table data will also be auto filtered and vice versa).

    To get rid of this limitation as "Many to Many" Relationship create an intermediate table as a Bridge table/Cross reference table which creates 3 tables overall maintaining One (One to Many) and second (Many to One).

    Hope this is helpful !!

    Thank you


    If this post helps to resolve your issue, please click the "Mark as Answer" of that post and/or click Answered "Vote as helpful" button of that post. By marking a post as Answered and/or Helpful, you help others find the answer faster.

    • Proposed as answer by Naveen Thummalapenta Tuesday, October 22, 2019 3:29 AM
    • Marked as answer by cataster Thursday, January 23, 2020 4:11 PM
    Friday, September 20, 2019 9:49 AM
  • Hi

    Just checking in to see if my initial answer helped or you still facing any issues.

     

    If my initial reply answers your query, do click “Mark as Answer” and Up-Vote for the same which might be beneficial to other community members reading this thread .

    And, if you have any further query do let us know.

    Thank you


    If this post helps to resolve your issue, please click the "Mark as Answer" of that post and/or click Answered "Vote as helpful" button of that post. By marking a post as Answered and/or Helpful, you help others find the answer faster.

    Tuesday, October 22, 2019 3:29 AM