Filtering with many-to-many relationship

[Anonymous]

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

[Andshepch]

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

[Anonymous]

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 · Accepted solution]

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]

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.

[Anonymous]

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

[d_gosbell]

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]

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

[d_gosbell]

Is that a test model that you could upload to somewhere? If you have a bi-directional many to many relationship I can't think why table2 filters table1, but not the other way around.

[Anonymous]

I copied the values from the tables and found out the directquery add's two spaces. I transformed the column with trim and it works..

Thanks!

[d_gosbell]

---

@Anonymous wrote:

I copied the values from the tables and found out the directquery add's two spaces. I transformed the column with trim and it works..

---

So technically I think you'll find that the extra spaces are in the source tables, not added by DirectQuery, but I understand what the issue is and I'm glad you found the solution. 🙂