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

Relation matrix -- not sure how to do it

  • 1.  Relation matrix -- not sure how to do it

    Bronze Contributor
    Posted 28 days ago
    Let's say that I have 3 tables
    * Customer
    * Product
    * Sales

    I want to show a relation that indicates who bought product A has also bought product B. So a distinct count on Customer, but how can I express the Product dimension twice?

        A , B , C , D
    A , 5 , 4 , 1 , 1
    B , 4 , 9 , 1 , 8
    C , 1 , 1 , 9 , 0
    D , 1 , 8 , 0 , 9

    It shows that
    - who bought product C has never bought product D
    - who bought product B has a high chance to buy product D


    How can I do that with the three tables I have?

    ------------------------------
    Gustavo Laufer
    Business Analyst
    ------------------------------


  • 2.  RE: Relation matrix -- not sure how to do it

    Bronze Contributor
    Posted 28 days ago
    I am thinking about duplicating the fact table (Sales), not sure whether this is the right approach

    ------------------------------
    Gustavo Laufer
    Business Analyst
    ------------------------------



  • 3.  RE: Relation matrix -- not sure how to do it

    Silver Contributor
    Posted 26 days ago
    A cheaper (less memory) way to do it would be to duplicate the Product Name column in the Product table and use that field in the Columns of the Matrix.  You would need to enable bi-directional filter in the relationship between the product and sales tables.  If you don't want to do this, then you should consider a Calculated table using DAX Crossfilter()

    ------------------------------
    Jeff Nixon
    Managing Director
    Jefferson Rand, LLC
    ------------------------------