Cross filtered tables - pivot with dimensions from multiple DIM tables

[serky]

Hi

I have a question wrt to an existing thread: https://www.mrexcel.com/forum/power-bi/1064965-dax-cross-filtering.html

The diagram below shows the model and the solution was:

[# Contracts] = CALCULATE ( COUNTROWS ( Contracts ), SuppliersProducts )

=

This all works fine but my issue is when I create a pivot table the data does not filter properly depending on the data selected.

1. if I have Products[Product Name] as a row filter and [# Contracts] in the body, it's OK
2. if I add Suppliers[Supplier Name] to the row filters, all the suppliers are listed against every Products[Product Name]. So if there are 100 suppliers in the Suppliers table, 100 Supplier Names are listed for each product. This is not correct as I should only get suppliers who provide a given product.

How do I show only the suppliers that sell a given product?

Thanks

 

[serky]

Please do not reply to this as I'm seeking other help and will post an answer when it's available.