Filtering a visualization off of multiple columns in a table with different datasources

[bakkek93]

I am currently using 3 tables.

Table 1 (FilterTable) covers a unique list of codes and has their start and end dates for when they are/were relevant.

Table 2 (Volume) covers the volume associated to those codes between a 7 day rolling date period (today and back one week, constantly updating).

Table 3 (Lookup) is used to join the two tables because in one spot they are “1” or “2” and in the other spot they are “Code 1” or “Code 2”.

My relationship currently works where I can click on Filter Table “Code 5” and the visualization built off of Volume will only show data related to “Code 5”. However, I would like to have the visualization also filter based on the FilterTable start and end date and I have no idea how to get that portion to work.

In the example included in my sample data below, I can click “7” in my filter table and it will highlight code 7 in the right visualization, but since the date range is 10/5-10/9, I’d like the visualization to also include the specified dates in the table as a filter criteria. Any help would be greatly appreciated! 😊

Volume Table

10/4/2018	Code 6	40
10/4/2018	Code 7	42
10/5/2018	Code 4	7
10/5/2018	Code 7	71
10/5/2018	Code 8	97
10/5/2018	Code 9	20
10/5/2018	Code 10	35
10/6/2018	Code 1	37
10/6/2018	Code 2	30
10/6/2018	Code 3	89
10/6/2018	Code 7	1
10/7/2018	Code 9	42
10/7/2018	Code 7	51
10/8/2018	Code 1	30
10/8/2018	Code 2	100
10/8/2018	Code 7	36
10/9/2018	Code 6	28
10/9/2018	Code 7	31
10/9/2018	Code 8	9
10/9/2018	Code 9	14
10/9/2018	Code 10	57
10/10/2018	Code 1	3
10/10/2018	Code 2	63
10/10/2018	Code 3	28
10/10/2018	Code 4	49
10/10/2018	Code 7	59
10/10/2018	Code 10	96

FilterTable

1	Here is a text field	8/1/2018	8/10/2018
2	Here is a text field	10/4/2018	10/6/2018
3	Here is a text field	10/1/2018	10/12/2018
4	Here is a text field	6/1/2018	6/10/2018
5	Here is a text field	10/10/2018	10/12/2018
6	Here is a text field	11/1/2018	11/30/2018
7	Here is a text field	10/5/2018	10/9/2018

Lookup Table

1	Code 1
2	Code 2
3	Code 3
4	Code 4
5	Code 5
6	Code 6
7	Code 7
8	Code 8
9	Code 9
10	Code 10

Relationships used:

1. FilterTable CodeName to Lookup Code. Many to One. Both.

2. Volume Table Code to Lookup Full Code Name. Many to One. Both.

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[bakkek93]

Hi Ashish_Mathur! Thanks for the reply! I am still unable to get it to work as expected.

The Filter table should have a start and end date and be used for filtering the visualization. I was hoping to learn how to join the filter table to the volume table on both of those dates so that when you click on Code 7 in the table, it only shows the dates where relevant. In the example below, since the start date and end date are 10/5 and 10/9 then we'd filter out 10/4 and 10/10 data. Is there a way to set date variables based on what selection you click in the table and then pass them to the visualization as only show data between startdatevariable and enddatevariable?

Thanks!!

[Ashish_Mathur]

Hi,

Does my solution not achieve that?  What happens when you build a visual in the file that i shared with you?

[bakkek93]

Hi Ashish_Mathur,

No I am not able to get the attached pbi to do what I need. The filter table needs to have a date range instead of just one date and it needs to filter the graph to the right when selected. I pulled out the filter table and the volume graphic below, but you can see when I click on a specific date, it doesn't filter the graphic to the right based on the selection. Does that make sense? Thank you so much for working through this with me - I really appreciate your help!!

 

[Ashish_Mathur]

Hi,

On the file that i shared with you, build your desired visual visual/slicers and share the download link with me.