Selective crossfiltering between visuals

[Anonymous]

Fellow BI collegues,

I am trying to have a way to limit the interaction between two visuals to limit to one selective dimension. To be precize, I would like to have a the date filtering to be excluded in the cross filtering.

What am I trying to achieve:

I have an Fact_Order list and a Fact_Sales list. Orderdate and saledate are not the same (sales are only considered a sale when they leave our warehouse rather than when the customer places an order). However, I am trying to show when orders are actually accounted as a sale and vice versa (when did orders of today's sale come in?).

Currently, when you select the orders of a certain date by pressing on this in the "Order" visual, these orders are indeed filtered in the "Sales" table. However, also the date will be filtered. That means I only see orders that have turned into sales on the same day.

I have tried a lot with creating different measures with "USERELATIONSHIP" and also a bridgetable but I do not manage to get the date cross filtering to be removed. I would appreciate your help on this a lot.

Attached I have provided a mockup power bi report where I have both tables provided, also showing the problem I am facing. Seem to be restricted to share mockup power bi reports with you. 

Thanks a lot!

[v-lid-msft]

Hi @Anonymous ,

If the relations between two tables based on the Date, it will act like yours, but if relations based on another column such as order ID, it should work fine.

If it doesn't meet your requirement, kindly share your sample data and excepted result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.

BTW, pbix as attached.

Community Support Team _ DongLi
If this post helps, then please consider Accept it as the solution to help the other members find it more

[Anonymous]

Thank you for your reply and you effort to even share a PBI example file.

However I am still struggling getting this to work with other dimensions. The both way relationship between the facts cause ambiguity when adding other shared dimensions. 

I am working on a bridge (factless) table to allow both side interactivity and with USERELATIONSHIP I am creating seperate measures. Apart I keep two seperate date dimensions so disable the axis to be filtered. Nevertheless, I feel like this is an extremely cumbersome way of achieving this and I also find it hard to foresee what other problems I am running into with this solution.

I find it hard to believe there is no more easier solution, such as Tableau "actions".

Thanks for your reply still!

[v-lid-msft]

Hi @Anonymous,

It should still work fine in a single way relationship (order -> sales), it may be helpful for DAX if you give simple description of two tables such as mockup column names.

You can share your mockup file mentioned before if you don't have any Confidential Information inside by  uploading your files to One Drive and share the link here.

Community Support Team _ DongLi
If this post helps, then please consider Accept it as the solution to help the other members find it more

[Anonymous · Accepted solution]

OneDrive has been locked by the administrator of my organization. I have used WeTransfer to share my file. I will also provide screenshots as WeTransfer will remain only temporary: WeTransfer Link for my PBI file

Nevertheless, the conclusion is that it seems to work with my way. However, god knows what trouble I am putting myself in with things I have not foreseen now. For example, time intelligence functions using one universal date dimension rather than two which I have now.

For now, I can crossfilter in both directions: Sales > Orders and Orders > Sales. Also, the date slicer works. The Orders and Sales table in this overview have different date dimension on their axis.

My model using the bridgetable looks like this. The inactive relationships are used for the DAX measure USERELATIONSHIP (see later screenshot how the particular measure looks like).

The bridgetable shows a unique combination of datekey and orderkey.

And here are my two sales and order measures.

Hope this helps and if anyone knows a simplier solution, please share!

Quick update:

If anyone knows a different way of sharing the desktop file. It could help other users and it is a pity Wetransfer only keeps the file for 7 days. Others are also very welcome to reshare my file who have better sharing capabilities.

[Greg_Deckler]

If you have a measure involved, you could potentially use ALLEXCEPT to remove certain filters.

[Anonymous]

I have tried the following dax formula:

NewSalesValue = CALCULATE([SALES];ALLEXCEPT('DimOrder';'DimOrder'[OrderKey]))

However, my date axis in the visual containing NewSalesValue is still restricted by the other visual. When I switch off the interactivity between the visuals, nothing happens any more. 

I do not believe there is no way or workaround to have this interactivity in Power BI to be customized but for now I see no way..

[darrenfishell]

@Anonymous I know this is an old thread, but seconded. It's really difficult to achieve the kind of specific filter action options available in Tableau's actions menu. 

And this is probably little help now, so far behind, but why not relate sales and orders with a bidirectional relationship on order ID? And then have a common date table with a single-direction filter to both the order and sales tables. 

If you used the date field from the actual order and sales tables in your tables, those don't have a relationship to each other, so that should just filter on orderid. If you needed a common date, you could use the date table.