Forum Discussion

Gregg's avatar
Gregg
Regular Visitor
10 years ago
Solved

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 ...
Need Help
  • Gregg's avatar
    Gregg
    10 years ago

    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.
     
     
    Thanks for your help.

     

Sean's avatar
Sean
Community Champion
10 years ago

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

 

 

Gregg's avatar
Gregg
Regular Visitor
10 years ago

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.
 
 
Thanks for your help.