Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Filtering with many-to-many relationship

Hi!

 

For the last three days I am trying to do some filtering on many to many related tables. Unfortunately I have not found a solution yet on this forum.  I have thee related tables

 

Table A > Excel import
Table B > DirectQuery SQL 

Table C > DirecyQuery SQL

 

Those three tables are related with an order_id field. PowerBI automatically recognizes the relation as 'many to many' , cross filter direction 'both' with security check box enabled. 

 

What I try to accomplish is that I want to show the records of table B which do no occur in table A. 

 

Methods I tryed which did not work:

- Merge queries with different join types.

- Calculated column.

- Enable 'Show items with no data'.

 

Whats is the correct way to achieve what I try to accomplish?

 

Thanks, Mark

Need Help
  • d_gosbell's avatar
    d_gosbell
    7 years ago

    You should be able to do this with your existing tables with a measure like the following:

     

    In Table1 missing in Table2 = 
IF( HASONEVALUE(Table1[OrderID]), 
  Var _table1OrderID = MAX(Table1[OrderID])
  Var _table2RowsWithTable1ID = CALCULATETABLE(Table2, TREATAS({_table1OrderID},Table2[OrderID]))
  Var _countOfMIssingRows = COUNTROWS(_table2RowsWithTable1ID)
  return IF(_countOfMissingRows = 0,"Missing")
)

     

    Returns a result like this

11 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi Mark,

     

    You can achieve this by having factless fact table inbetween your tables (table A has many to many relationship with table B).

     

    Table A --> factlessfact table --> Table B

     

    Factless fact table should have distinct ids from table B.

     

    And Table A(id) --> factlessfact(Distinct ids of Table B) --> table B

     

    Relationship between factlessfact table and table B should be in bidirection mode.

     

    Regards,

    Pradeep

    • d_gosbell's avatar
      d_gosbell
      Icon for Super User rankSuper User
      7 years ago

      You should be able to do this with your existing tables with a measure like the following:

       

      In Table1 missing in Table2 = 
IF( HASONEVALUE(Table1[OrderID]), 
  Var _table1OrderID = MAX(Table1[OrderID])
  Var _table2RowsWithTable1ID = CALCULATETABLE(Table2, TREATAS({_table1OrderID},Table2[OrderID]))
  Var _countOfMIssingRows = COUNTROWS(_table2RowsWithTable1ID)
  return IF(_countOfMissingRows = 0,"Missing")
)

       

      Returns a result like this

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        This looks like a nice solution. I added a new measure but I just says for every order_id it is missing. My order id are type text, can this cause a problem? Changed MAX to MAXA and no difference. 

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi Mark,

     

    What I do is create a calculated table using UNION and DISTINCT

     

    Table = DISTINCT(UNION(DISTINCT('Table A'[order_id]),DISTINCT('Table B'[order_id]),DISTINCT('Table C'[order_id])))

     

    Then create a relationship[ between Table A, Table B and Table C and your new table

     

    Then you can create a visualisation table of Table B, and insert a filter of the order_id from Table A, and filter it for Blanks

     

    I think that should work.

     

    Regards

     

    Andrew