cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PBInath
Advocate II
Advocate II

Selective crossfiltering between visuals

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?).

 

Knipsel1.PNGKnipsel2.PNG

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!

 

1 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.

1.PNG2.PNG

 

 

 

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).

3.PNG

 

The bridgetable shows a unique combination of datekey and orderkey.

 

4.PNG

And here are my two sales and order measures.

5.PNG

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.

View solution in original post

7 REPLIES 7
v-lid-msft
Community Support
Community Support

Hi @PBInath ,

 

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.

 

Selective-crossfiltering-between-visuals-1.pngSelective-crossfiltering-between-visuals-2.png

 

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

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

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!

Hi @PBInath,

 

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

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

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.

1.PNG2.PNG

 

 

 

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).

3.PNG

 

The bridgetable shows a unique combination of datekey and orderkey.

 

4.PNG

And here are my two sales and order measures.

5.PNG

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
Super User
Super User

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


@ me in replies or I'll lose your thread!!!
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Learn Power BI 2nd Edition

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..

@PBInath 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.

Helpful resources

Announcements
June 2022 update 768X460.jpg

Check it out!

Click here to read more about the June 2022 updates!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors