Crossfilter with multiple fact tables

[av9]

Hi 

I am looking to get some advice on the best way to proceed with this model.

I believe my 2 options are : 

1) Use DAX Crossfilter function

2) Turn on bi-directional filtering (i.e. Both) in the model  

I have a matrix table and it contains data from Customer, Transactions and Stock Price Email , Monthly Newsletter.

I tried to build the coumns of the matrix using DAX Crossfilter function, for example:

Company (Matrix) = CALCULATE (
 SELECTEDVALUE ( Customer[Company] ),
 CROSSFILTER ( Transactions[CustomerID], Customer[CustomerID], BOTH )
)

This works when filtering by Customer columns, but when I try and filter by Stock Price Email or Monthly Newsletter the whole matrix table is not filtered , just the columns Stock Price Email or Monthly Newsletter. this is the DAX.

Stock Price Email Status (Matrix) = CALCULATE (
 SELECTEDVALUE ( 'Stock Price Emails'[Status]),
 CROSSFILTER ( Transactions[CustomerID], Customer[CustomerID], BOTH ),
 CROSSFILTER ( 'Monthly Newsletter'[CustomerID], Customer[CustomerID], BOTH ),
 CROSSFILTER ('Stock Price Emails'[CustomerID], Customer[CustomerID], BOTH )
)

I am not sure if I have applied the Crossfilter function correctly. My alternative is to turn on bi-directional filtering on these tables which works, but I want to ensure I follow best practice and this is why I am looking for any guidance.

I have attached the PBIX I am working with.

https://drive.google.com/file/d/1hNamdGFS4_ohIY2gsaO9dtgy6c2vWN4K/view?usp=sharing

Thanks in advance.

[amitchandak]

@av9 , what you want to filter , say if you want customers from transaction that has newsletters

countx(values(customer[customer]), if(not(isblank([newsletter measure])) && not(isblank([transaction measure])) , [customer], blank())

So what you wnat to get here, please let us know.

[av9]

I want to be able to filter the matrix by the status. So in the screenshot 1 if I pick the slicer it should remove rows I marked in red. I can achieve this by using bi-directional filter but want to get some advice if this is the right way to go or should I use DAX.

this is what it should look like:

[v-stephen-msft]

Hi @av9 ,

You can set the value is not blank in Filters.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[av9]

Thanks, it probably doesnt show in this example report but I also have some customers who have blank as there preference. 

So even when I apply this filter and slicer it filters the column but the ones that are blank still show.

Crossfilter example