Solved: Filtering with many-to-many relationship - Page 2

Anonymous · ‎03-05-2019

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

d_gosbell · ‎03-05-2019

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

View solution in original post

Filtering with many-to-many relationship

Helpful resources

Microsoft Fabric Learn Together

Power BI Monthly Update - April 2024

Fabric Community Update - April 2024

How to Get Your Question Answered Quickly