DAX cross filtering

[serky]

Hello
I'm not sure this is the right title but I'm hoping someone can help. I have a very basic model re supplier data (2 dimension tables and 2 fact tables). I have listed the tables and columns below.

Dimensions
(Supplier list) - supplier ID
(Product list) - product ID

Facts
(Supplier products) - supplier ID, product ID
(Contract list) - contract ID, supplier ID

My issue is how to ensure slicers, calculations and pivot tables work correctly given that the user will be be able to filter with slicers from the supplier list and/or product list. Slicing with (supplier list [supplier ID]) will filter both fact tables correctly but slicing with (product list [product ID]) will only filter the (supplier products) table.

In most cases, I need the filter to work as follows:
1. pick a (product list [product ID])
2. filter the (supplier products) table to show relevant suppliers with that product
3. filter the (supplier list) table based on the results from point 2
4. filter the (contract list) table based on the results from point 3

How do I ensure that the supplier slicer only shows suppliers with the chosen product?
How do I ensure that a report/table only shows the relevant suppliers for each product?
How do I ensure that a measure eg - contract count, is for the relevant suppliers and products?

What is the best way to manage this? Do I need to change the model? I have browsed the internet but cannot find a simple example.

I am using Power Pivot in excel.

Thanks

 

[VBA Geek]

From what I understand you must have a model like the one below, where SuppliesProducts is a bridge table.

In Excel you have two ways to have the SuppliersProducts table filter the Supplier table which in turn automatically filters the Contracts table :

Table Expansion:
=<br><span class="Keyword" style="color:#0070FF">CALCULATE</span><span class="Parenthesis" style="color:#969696"> (</span> <span class="Keyword" style="color:#0070FF">COUNTROWS</span><span class="Parenthesis" style="color:#969696"> (</span> Contracts <span class="Parenthesis" style="color:#969696">)</span>, SuppliersProducts <span class="Parenthesis" style="color:#969696">)</span><br>

CROSSFILTER:
=<br><span class="Keyword" style="color:#0070FF">CALCULATE</span><span class="Parenthesis" style="color:#969696"> (</span><br><span class="indent4">    </span><span class="Keyword" style="color:#0070FF">COUNTROWS</span><span class="Parenthesis" style="color:#969696"> (</span> Contracts <span class="Parenthesis" style="color:#969696">)</span>,<br><span class="indent4">    </span><span class="Keyword" style="color:#0070FF">CROSSFILTER</span><span class="Parenthesis" style="color:#969696"> (</span> SuppliersProducts[SuppliedID], Suppliers[SupplierID], <span class="Keyword" style="color:#0070FF">BOTH</span> <span class="Parenthesis" style="color:#969696">)</span><br><span class="Parenthesis" style="color:#969696">)</span><br>

 

[serky]

Hi @VBA Geek

thank you for your answer and sorry for taking so long to get back you the trials and tribulations of trying to get Power stuff to work!

Your answer has worked perfectly but would you be able to clarify a couple of things (or point me to some reference material):
1. the 'table expansion' option doesn't have a filter direction list CROSSFILTER. Does this mean it assumes 'both' directions?
2. I'm not clear what the filter direction in CROSSFILTER is actually doing. I have read https://msdn.microsoft.com/en-us/query-bi/dax/crossfilter-function. Is the following correct:

a. the CROSSFILTER function is referring to the relationship between the 2 columns in the CROSSFILTER brackets (in this case it would be SuppliersProducts[SuppliedID] and Suppliers[SupplierID])
b. 'none' means filtering 'Suppliers' will filter 'SuppliersProducts' and 'Contracts' in the normal way. The measure would just count the # rows from 'Contracts' based on the Suppliers' filter.
c. 'both' means that filtering 'Suppliers' will filter 'SuppliersProducts' OR filtering 'SupplierProducts' will filter 'Suppliers'. If only 'Suppliers' had been filtered, the result would be the same as b. but if 'SuppliersProducts' had been filtered (from 'Products') then it would filter 'Suppliers' and 'Suppliers' would filter 'Contracts' ie - the answer would be different to b.
d. What is 'one' doing then? The explanation states that 'filters on the one or lookup side of the side of the relationship filter the many side'. How is this different to 'none'?

Just a little confused ...

Thanks

 

[VBA Geek]

Simply put, the theory behind table expansion is that each table in your data model is expanded to contain all the columns of the tables from the one-side of the relationship.

Thus, if you have a simple model like the one below:

The expanded Sales table is:

When you want to calculate the Sales for January, you can think that the Expanded Sales table gets filtered by January.
This expanded table can be used in the filter argument of CALCULATE to "filter" a table on the one side of the relationship.
For instance if you wanted to know which products are present in the sales table, you can do: CALCULATETABLE( Products, Sales ).
Or which products have been sold during 2010: CALCULATETABLE (Products, CALCULATETABLE( Sales, 'Date'[Year] = 2010 ) )

You will find a better and longer analysis in this book ( chapter 10 ).



Normally you will use CROSSFILTER just with BOTH as last argument in order to activate bi-directional filtering (in POWER BI you can do it by editing the relationship itself ) from many to one side.
In my previous post, SupplierProducts could normally not filter Suppliers, but by activating the Crossfilter you can reach the suppliers table via the products table via a many to many relationship.