Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
Expand all | Collapse all

Many-to-Many Relationship

  • 1.  Many-to-Many Relationship

    Silver Contributor
    Posted 12 days ago
      |   view attached
    Hi,

    Referring to the attachment, can someone please explain to me in layman terms for many-to-many cardinality, what do we mean by single and both cross filter direction? What is the implication if I select single of table 1 vs single of table 2 vs both directions?

    Thanks!

    ------------------------------
    Chong SM
    ------------------------------
    Academy - Online Interactive Learning from Experts


  • 2.  RE: Many-to-Many Relationship

    Top Contributor
    Posted 12 days ago
    What business questions are you planning to ask of your data? Will you filter the contents of one table based on a selection in the other? The other way round? Both?

    With your business question in hand, trace your data model for the tables required to answer it, and check that the filter directions facilitate the answer instead of making it impossible.

    ------------------------------
    Lutz
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 3.  RE: Many-to-Many Relationship

    Posted 10 days ago
    In this situation, Cardinality refers to the relations ship between the 2 tables.  Many to Many will cause alot of headaches and you should not enable it unless you have a specific business need to.  The One side should always be the dimension side, and the Many should always be the Fact Table.  If you are not clear about these concepts then your results will probably give you the wrong numbers and get you into trouble.  Stick with Star Schema and try to understand the model before you attemp to build any reports.   Microsoft recently release some information about modeling which you may find helpful.  https://docs.microsoft.com/en-us/power-bi/guidance/star-schema

    The Cross Filter Direction refers to which way you want to filters to flow.  You should never use Both, unless you absolutely understand what you are doing.  The normal way to set this up is to filter from the dimension table to the fact table, the one side to the many side.  Use the column from the dimension table in your slicers and filters.

    Read the article about star schema and try to build something simple first, .. add 1 table at a time and see how it affects your measures.  That is the best way to do it.  Turn off Power BI's automatic relationship detector.. this can also cause problems if you are not familiar with Data Modeling.

    ------------------------------
    Ka Ly
    Clinical Informatics Program Manager
    ------------------------------

    Academy - Online Interactive Learning from Experts