Forum Discussion

av9's avatar
av9
Icon for Helper III rankHelper III
5 years ago

Crossfilter with multiple fact tables

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.

Need Help

4 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    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's avatar
      av9
      Icon for Helper III rankHelper III
      5 years ago

      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:

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        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.