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
RandomKitten
Frequent Visitor

Selective filters- Limiting interactions to specific fields?

I have two visuals that I would like to make interactive.  One is a table that lists items for the current week, and the other is a line chart that shows the trend of said items over the past 6 months.

What I WANT: 

When you click on an item in the table, the line chart should filter to that item, but keep its original date range of 6 months so you can see the trend of items over time.

What I GET:
When you click on an item in the table, the line chart shows only one data point for the current week's total, because it filters by item AND by date.

Is there any way I can limit the interactions to only work on the items, and not filter by date?  

(BTW the actual scenario is more complex, this is a simplified question for the sake of understanding if it's possible.  I'd like to avoid forcing the users to use slicers if possible because of the additional complexity not mentioned here.)

1 ACCEPTED SOLUTION
RandomKitten
Frequent Visitor

I seem to have found a partial solution by accident, so sharing here (since Googling found no solution earlier):

 

Change the Table to a Matrix and click the arrow at the top to turn Drill Mode on.  

 

Caveat: You have to have multiple fields set as the Rows, and you can't filter using the bottom layer. 

 

For example, I have Category, Subcategory, and Item - I can click the Category or Subcategory, and the Line Chart will filter to only show that category/subcategory, but clicking Item will not filter the Line Chart to that item.

 

Fortunately, this is good enough for now, but if there's any recommendations on how to get it down to that bottom level, I'd still appreciate the help!  

View solution in original post

3 REPLIES 3
RandomKitten
Frequent Visitor

I seem to have found a partial solution by accident, so sharing here (since Googling found no solution earlier):

 

Change the Table to a Matrix and click the arrow at the top to turn Drill Mode on.  

 

Caveat: You have to have multiple fields set as the Rows, and you can't filter using the bottom layer. 

 

For example, I have Category, Subcategory, and Item - I can click the Category or Subcategory, and the Line Chart will filter to only show that category/subcategory, but clicking Item will not filter the Line Chart to that item.

 

Fortunately, this is good enough for now, but if there's any recommendations on how to get it down to that bottom level, I'd still appreciate the help!  

RyanAck
Advocate I
Advocate I

Hi, I think I get what you're saying.

 

I'm sure there are multiple ways to do this, but my solution would be to create a measure for the rolling 6 month value. That way, when you click the item in the week, you're not seeing just that week's sales in the other chart, you're seeing the 6 month trend for whatever item you've selected.

 

It's hard to say specifically what that measure would look like, but it would be a CALCULATE() where the filter forces it to use the 6 rolling months. 

 

This would give you two measures, the one in your table you click, and another one in the chart for rolling 6 months value, disregarding the week date. 

Thanks, RyanAck.  The concern I have with that solution is that a Measure would still only give me one value, unless I create measures for Last Month, The Month Before, Two Months Before, etc. 

Since I want to be able to use the filtered data in a line graph, I need to preserve the date filter instead of creating a custom measure.  

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.