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 >...
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

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