Using a customized filter for unrelated tables

[Anonymous]

Hi!

I need help in with the following issue:

I currently have 3 unrelated tables and 1 related. tbl_Schedule, tbl_LineList and and tbl_calendar, tbl_TCARD

- tbl_schedule is a project schedule from SharePoint.

- tbl_LineList is connected to a view in an Oracle database.

- tbl_calendar uses the min start date and the max as due date from tbl_schedule:

                                                 CALENDAR(MIN(tbl_Schedule[StartDate]),MAX(tbl_Schedule[DueDate]))

- tbl_TCARD is derived from linelist with only column that should apply to the report. It currently has a One-to-one (1:1), Cross filer direction-both with tbl_linelist.

The issue right now is that when I select only 1 subcon from the filter,  that 'Calc TCARD Actual' column does not change based on the subcon filter that is applies to. 

The intent is that when a filter Subcon is selected, all columns should update based on the selected filter. Is that possible to have a filter that works if not all tables are related?  

 I also tried establishing a relationship between tbl_calendar[Date] = to tbl_TCARD [Date] and the filter started to work for for 'Calc TCARD Actual', but it stopped stopped working for 'Baseline per Day' and 'Calc TCARD Baseline Cumulative' (the other two columns). - All Columns are coming from the tbl_calendar. In addition, in order to get tha actuals to even display, I has to = the dates from both dates from tbl_calendar and tbl_TCARD.

  

   Calc TCARD Actual = SUMX(FILTER(tbl_TCARD, tbl_Calendar[Date] = tbl_TCARD[TCARDDATE]),tbl_TCARD[Calc TCARD Complete])

Selecting: Paragon

Selecting: Suncoast

The filter column is currently stored in tbl_TCARD.

 Here is the formula for the filter:

Calc ROW Subcon = IF([ROUTE] = "A 1" || [ROUTE] = "B 2" || [ROUTE] = "C 6" || [ROUTE] = "Chocolate D7" || [ROUTE] = "CP Chem Lateral" || [ROUTE] = "OCL 8" || [ROUTE] = "Stratton","Suncoast",IF([ROUTE] = "G3" || [ROUTE] = "Spread 4"          || [ROUTE] = "Spread 5","Paragon",BLANK()))

[v-yuta-msft · Accepted solution]

@Anonymous,

Filter only works when there exists explicit or implicit relationship between tables. If the first three tables don't have relationship, you may create some bridge tables to connect these tables so that one table will filter other tables.

Regards,

Jimmy Tao

[v-yuta-msft · Accepted solution]

@Anonymous,

Filter only works when there exists explicit or implicit relationship between tables. If the first three tables don't have relationship, you may create some bridge tables to connect these tables so that one table will filter other tables.

Regards,

Jimmy Tao

[Anonymous · Accepted solution]

I was thinking there might be another way, but did end up creating a relationship bridge between the subcon and the table that holds my actuals. In order to get my Sucbon, I had to create a table sorting filter that holds the Route since this is the column that my customize column derives it's data from. From there I established a relationship ( 1:*) from the 'Route' column in the filter table (several of my other tables use Route too) to the tbl_TCARD ( that holds the actuals). tbl_TCARD [Date] has a *:1 relationship with tbl_calendar[Date] in order for the actuals to fall under the right dates when being filtered.