Create a new appended (union) table that responds to filters from original tables

[kamiluc]

Hi there,

I’m creating a report for a controls process where remediation plans (RemPlans) get created through different processes.  For example, a control can have a remediation plan created directly against it, or it can be created as a result of a testing on this control.

This causes my data model to be something like this:

Now, I wanted to create a single table in my report showing a list of remediation plans. I’ve created a calculated column in each RemPlan table to tell me the source of the RemPlan, and used UNION() to append the tables. 

My problem is that this calculated table doesn’t respond to filters applied to the original tables, as it’s not connected to the model.  Such a simple task but I haven’t been able to find a way to solve this.  My table visual should look like this:

Source	Control_ID	RemPlan_ID	Status
Control	ABC	123	Closed
Test	ABC	456	Open

I’ve searched other posts but couldn’t find quite the right solution. Any help is greatly appreciated!

[kamiluc · Accepted solution]

I've found a solution for this that works in my case.

As mentioned in the original post, I created a table combining the remplans using UNION() - "combined_remplans".

I then created a measure like the one below and I then pass it to the table visual where I bring the columns from my union table. This filters based on the filters applied to the page:

RemPlan filter = 

var rp1 = VALUES(RemPlan1[RemPlan_ID])

var rp2 = VALUES(RemPlan2[RemPlan_ID])

var rp_combined = UNION( rp1, rp2)

return

IF( SELECTEDVALUE('combined_remplans[RemPlan_ID]) IN rp_combined, 1, 0)