Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Cross filtering between 2 tables with a bridge table not working as expected

Hi all,

 

I have an interesting problem here. I am currently doing a word cloud visual along with some service tickets, connecting them with a bridge table created in Power Query. I used Python to generate a table of the top 100 words by frequency to speed up processing external to PowerBI and imported that in. My tables are as follows:

 

Case Table (A)

Case ID, other details, etc

 

Words Table (B)

Case ID, Word

 

Bridge Table (C)

Case ID

 

There is a value called "New document" under case ID, and that applies to cases where a case ID has not been issued yet. As a result, there are some words that have new documents as the case ID and that is bringing in all of the cases with "New document" from the case table which is incorrect. So I went into the Bridge Table to delete "New Document" from it, so now it is only a table with unique true case ID values. However, the filter behaviour isn't changed, and New Documents are still coming into my detail table. Any suggestions or hypothesis for me to try?

4 REPLIES 4
edhans
Super User
Super User

I would need to see your file to really test it out, but it seems you have bi-directional filtering enabled. Make sure that each relationship from the data tables to the bridge tables are many (data) to one (bridge).



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
Anonymous
Not applicable

I have attached a sample file here. When I click on the word cloud and select Wifi, I expect nothing to be returned as the only line with Wifi is a new document, and new document is not in the bridge table. However, I find that it is returning all the records with new document as the case id.

 

https://files.fm/u/fc5ddyb5

 

I think the fix for you is to simply remove Case_ID from your grid in Values that is coming from the "Words" table and replace it with CaseID from the "Bridge" table. That gives this, which you can sort however you like.

 

If taht is not what you want, can you give a brief sample of what you expect the results to be, but this excludes the NEW items, which is what I think you  were getting at.

 

tmp.png



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Well nope. I just realized when I sorted it it still had the empty values. You'll also need to set the Visual Level Filters for the visual to:

  • Case_ID
  • Advanced Filtering
  • Is Not Blank


Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.