how to use DAX or other ways to implement prefiltered slicer(slicer choices dynamically changing)?

[greatzt]

I know prefiltered filter can be implemented by using bi-direction relation between dimension and fact tables.  However, in a multi-facts table model, you cannot not make all the relation ship bi-direction since it will produce ambiguous filtering path. The following is an example.

The FactPrice table has three dimensions: Supplier, Product and Terminal. The FactCotract has two dimension: Supplier and Terminal.

The supplier and terminal are common dimensions of there two fact tables.  Now relationship between Terminal and FactPrice  are bi-direction and relation between Supplier and Factprice can't be set as bi-directoin since it will generate ambiguous filtering path. 

The following is a simple report based on this model. 

Since Terminal is bi-direction filtered to FactPrice , when user choose Proudct or Supplier slicer, the Terminal slicer will be prefiltered or change dynamically.  Because Proudct and Supplier are only single direction related to fact PriceFact table,  when user select Terminal slicer, Product and Supplier slicer will not be prefiltered.  What I want to do is to make all these filter prefiltered by using DAX or other workaround.  Does anyone has any ideas? . The pbix file can be found here. 

[v-lili6-msft]

hi, @greatzt

It couldn't achieve by using DAX or other workaround and only can be affected by using bi-direction relation.

For your data model, It has two relation route from Supplier to Terminal. you don't need to create a relationship between FactContract and Terminal.

So try to romove it and make the relationship between Supplier and Factprice to be set as bi-direction.

Best Regards,

Lin

[greatzt]

@v-lili6-msft  I need to keep the contract fact table since the fact table relies on Terminal and Supplier dimension. I can't combine two fact table either because contract does not involve Product dimension.  since PowerBI does not allow visual filter on slicer, I can't use  customer measure to filter slicer.  I will Power BI can support it as the some BI  tool do.

[v-lili6-msft]

hi, @greatzt

You may try to Append the FactContract and FactPrice in Power Query or Use Union Function when Modeling data.

Be careful about column order of two table

For example:

Best Regards,

Lin

[greatzt]

@v-lili6-msft Thank you for input. Your soluation is  probably working for small dataset.  However,  If  FactPrice table is big (million rows) and Factcontract table has some dimensions which are not applied to Factprice table,  the  combined fact table will become very large since there will be many repetitive info. 

[Stachu]

compression in Tabular is really good, so having the table appended together shouldn't be a real issue - from what I see only few columns are not overlapping

otherwise you can try INTERSECT/TREATAS
https://www.sqlbi.com/articles/physical-and-virtual-relationships-in-dax/

[greatzt]

any input for this question?