cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Gregg
Regular Visitor

Filter another visual by different field

I want the visuals to interact in such a way that only some of the filters in the selected visual apply to the other visual, while other filters do not.

 

Here's an example. The data is shown here (Date, Name, Amount). The Bar chart represents Name and Amount, and is filtered by date (2016-03-03). Assume there are hundreds of names.

PBI Drilldown Example 5.PNG

 

PBI Drilldown Example 2.PNG

 

The bar chart shows that H is has the larest value for 2016-03-03. So I want to select H to see only H's time series in the line chart and table. However both those visuals are filtered by 2016-03-03 and H, instead of just H. This behavior is expected in some scenarios, but how do I change hat behavior to show H for all dates?

 

PBI Drilldown Example 3.PNG

 

 

When I select H, the bar chart and table should filter like this:

 

PBI Drilldown Example 4.PNG

 

Is this possible natively? Otherwise I'm wondering if there is a way to create a DAX measure which will somehow "force" the line chart\table to unfilter by date. I tried doing this but had no success.

 

Thanks.

1 ACCEPTED SOLUTION
Gregg
Regular Visitor

That worked @Sean. I made a few modifications so that the filter is more dynamic, and can work for any date (not just months). Here are the steps I took.

1. Create a measure "Selected Date" which is the date selected.
- Here we use MAX(Date), given that if one date is selected MAX(Date) will equal the date selected
Selected Date = MAX(Data[Date])
2. Create a measure "Filtered Amount"
- This is the amount, filtered by the "Selected Date". Use this Filtered amount on the bar chart, instead of placing a filter on the visualization
Filtered Amount = CALCULATE(SUM(Data[Amount]), FILTER(Data, Data[Date] = [Selected Date]))
3. Add a date slicer to the chart so we can select the date that drives the bar chart.
- Edit interactions so that the Line Chart and Table are not filtered by the date selected on the Date Slicer.
 
4. Now when you select a date on the Date Slicer, the bar chart should filter by date, since it's using "Filtered Amount".
The Line chart and table should not filter since interactions are off for those two, and the chart\table reference [Amount]  and [Date] (not [Filtered amount] and [Selected Date])
When a bar is selected (eg "H" bar), the Line chart & Table will filter by Name = "H" only, and not by Name and Date.
 
PBI Sample8.JPG
 
Thanks for your help.

 

View solution in original post

7 REPLIES 7

@Gregg Based on your description I think controlling the interaction of the visuals will solve the issue.

Check out this link


Looking for more Power BI tips, tricks & tools? Check out PowerBI.tips the site I co-own with Mike Carlo. Also, if you are near SE WI? Join our PUG Milwaukee Brew City PUG

Thanks @Seth_C_Bauer 

While that link is useful, it does not quite address what I need. The options there are either

  1. Filter (assuming by all filters aplied to the selected bar chart), or
  2. Do not filter

What I need is filter by only one of the filters applied to the bar chart.

 

The Highlight option is not available on the Line chart (in this case... I'm not sure why) - see screenshot. Highlight would not be ideal anyway, because the time lines might to be scaled differently.

PBI Drilldown Example 6.PNG

Sean
Community Champion
Community Champion

@Gregg Create a new measure like in the picture and get rid of the Visual Level Filter

(I've created a custom column Year-Mo = FORMAT(Table1[Date], "YYYY-MM") which I use to filter only March)

You can then even add to the bar chart your SUM of Amount so you have a TOTAL bar and March only bar as in 2nd picture

EDIT: You can of course improve the filter part of the Measure based on another custom column 

something like => FILTER(Table1, Table1[Custom Column]="Current Month")  or "Previous Month" etc...

 

Filter by Another.png

 

Filter by Another2.png

Gregg
Regular Visitor

That worked @Sean. I made a few modifications so that the filter is more dynamic, and can work for any date (not just months). Here are the steps I took.

1. Create a measure "Selected Date" which is the date selected.
- Here we use MAX(Date), given that if one date is selected MAX(Date) will equal the date selected
Selected Date = MAX(Data[Date])
2. Create a measure "Filtered Amount"
- This is the amount, filtered by the "Selected Date". Use this Filtered amount on the bar chart, instead of placing a filter on the visualization
Filtered Amount = CALCULATE(SUM(Data[Amount]), FILTER(Data, Data[Date] = [Selected Date]))
3. Add a date slicer to the chart so we can select the date that drives the bar chart.
- Edit interactions so that the Line Chart and Table are not filtered by the date selected on the Date Slicer.
 
4. Now when you select a date on the Date Slicer, the bar chart should filter by date, since it's using "Filtered Amount".
The Line chart and table should not filter since interactions are off for those two, and the chart\table reference [Amount]  and [Date] (not [Filtered amount] and [Selected Date])
When a bar is selected (eg "H" bar), the Line chart & Table will filter by Name = "H" only, and not by Name and Date.
 
PBI Sample8.JPG
 
Thanks for your help.

 

View solution in original post

konstantinos
Memorable Member
Memorable Member

@Gregg  This is normal because when you select the bar of H it has 2 filters ( Date - on visual level & H as name ). 

 

You can replace the measure on the line chart so it doesn't filter by dates.

 

Btw it is better to have always a Date table as dimention.

 

Timeline =
CALCULATE ( SUM ( Table1[Amount] ); ALL ( Table1[Date] ) )
Konstantinos Ioannou

@konstantinos I replaced Amount on the Line chart with the TimeLine measure. This has not changed the behavior - it is still filtering by Date (and Name), as in my second screenshot. I tried adding a date table, and adding a relationship to my fact table, then using then applying all to dimDAtes[Date]. That does not work either.

 

TimeLine = CALCULATE(SUM(Data[Amount]), ALL(dimDates[Date]))

 

Am I missing a step?

 

I realize the current behavior is normal, but it would be good to override this behavior, in this particular case. 

 

Thanks for your help.

This is strange..At least for the demo fata and schema works in my end.
Unless you have a different schema with Other dimension tables which might affect - or/ and bi directional relationships which affects crossfiltering.
Do you have the filter on date in Page or Report filter?

Konstantinos Ioannou

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!