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

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's avatar
d_gosbell
Icon for Super User rankSuper User
7 years ago

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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Thanks, model is working nice. Somehow my own model does not work... can it be something with directquery?

     

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

      No, I don't think DirectQuery would matter. I added a DirectQuery table to my test model and it worked fine. (I just added the text "<Found>" to the else section of the IF statement so that I could see the matched rows)

      In SalesOrder1 missing in Internet Sales = 
IF( HASONEVALUE(SalesOrders1[SalesOrderID]), 
  Var _table1OrderID = VALUES(SalesOrders1[SalesOrderID])
  Var _table2RowsWithTable1ID = CALCULATETABLE('FactInternetSales', TREATAS({_table1OrderID},'FactInternetSales'[SalesOrderNumber]))
  Var _countOfMIssingRows = COUNTROWS(_table2RowsWithTable1ID)
  var _result = IF(_countOfMissingRows = 0,"Missing","<Found>")
  return _result
)

      Are you sure that the hyphen characters are identical between the two data sets and that there is no trailing whitespace or anything like that?

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        Thanks for putting some much time and effort into this question. I still have the same problem. See attached .gif.