Forum Discussion
Filtering with many-to-many relationship
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
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
Super User
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
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.