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
Super UserYou 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.
- d_gosbell
No, the data type shouldn't make any difference. I just changed my test model to text OrderID's
You can download my simple test model from my onedrive here if you want to have a look at it.
Thanks, model is working nice. Somehow my own model does not work... can it be something with directquery?