Filtering issues when using inactive relationship to calculate measure

[christinamarie]

Right now I have 3 tables:

1. Stock Data - a list of stocks, the week they were purchased, a boolean field to indicate whether they have been sold, and if true, the week they were sold

2. Stock Pictures - a list of all 16 different stocks and a URL of a corresponding image of that stock

3. Week Table - a table with a single column (weeks listed 1-5)

I wanted to be able to show a table of the count of each stock purchased and a table holding the count of each stock that had been sold. However, when a week was selected, I wanted to see the number of stocks sold on that week, not the number of stocks purchased on that week that eventually went on to be sold, which is what would've been shown if I used 'Stock Data'[Week] in the slicer. To solve this, I created the Week Table, placed 'Week Table' [Week Number] in the slicer, created an active relationship between 'Stock Data' [Week] and 'Week Table' [Week Number], created an inactive relationship between 'Stock Data [Week Sold]' and 'Week Table' [Week Number], and then finally created the following measure:

Sold Stock = CALCULATE(COUNT('Stock Data'[Share]), 'Stock Data'[Is_Sold] = TRUE(), USERELATIONSHIP('Stock Data'[Week Sold], 'Week Table'[Week Number]))

The setup works well, but I run into problems when I put an ImageViewer visual on top of each table and try to display the picture of the top sold stock by assigning the Top N visual level filter "Top 1 Sold Stock". Week 4 works as expected, but when I try to slice on Week 3, the picture of the top stock doesn't show up for some reason: 

I tried a few test table visuals out to see if any joins were going wrong, which you can see below. If I create a table with the Stock Name, Stock Image URL AND Sold Stock measure, Stock P (the sold leader for Week 3) shows up. However, if I make the same table, but without the Sold Stock measure, Stock P doesn't appear. I suspect this has to do with the fact that zero shares of Stock P were purchased during Week 3, but I can't figure out how to fix this. Any help would be appreciated!

[Seward12533]

Can you share what the relationships between your table and confirm which value of WEEK your using in your tables.  

In Week 3 Stock P does not show up in both tables so I'm guessing you have  a bi-directionalal relationship so its cross filtering and since you have no trades in that week PowerBI is filtering it out. Try chaning the cross filter from BOTH to Single and be sure your using the WEEK from your bridge table in your slicer. 

In Week 4 E is in both.