Connected tables problem

[stevedata]

I have 3 target lists; each target list has company names. Some company names appear in multiple target lists

target list A		target list B		target list C
company 1		company 2		company 5
company 2		company 3		company 6
company 3		company 4		company 7
company 4		company 5		company 8
		company 6

I have 2 datasets/tables one showing email clicks and the other social clicks

Email data table			Social data table
Company	clicks			Company	clicks
company 2	8			company 1	46
company 3	43			company 2	32
company 5	67			company 4	6
company 6	3			company 6	3
company 7	24			company 8	5

I want to create a filter by target list, essentially if I select the target list, then I want to see a combined table showing email clicks and social clicks for companies in the target list A

Target list groups filter

target list A

target list B

target list C

When filter 'target list A' is selected i want to see this table 

target list A
Company	Email clicks	Social clicks
company 1		46
company 2	8	32
company 3	43
company 4		6

I have a company names table with all unique company names - If this to both the email and social tables, and each of the target lists will that allow me to achive the table above?

Company names table

company 1

company 2

company 3

company 4

company 5

company 6

company 7

company 8

[Anonymous · Accepted solution]

Hi @stevedata ,
Based on your description, first create the following relationships in these tables

Create field parameter

Parameter = {
    ("target list A", NAMEOF('target list A'[target list A]), 0),
    ("target list B", NAMEOF('target list B'[target list B]), 1),
    ("target list C", NAMEOF('target list C'[target list C]), 2)
}

Create a mesure

Measure = 
SWITCH(
    TRUE(),
    SELECTEDVALUE(Parameter[Parameter Order]) = 0,IF(SELECTEDVALUE('target list A'[target list A]) = BLANK(),0,1),
    SELECTEDVALUE(Parameter[Parameter Order]) = 1,IF(SELECTEDVALUE('target list B'[target list B]) = BLANK(),0,1),
    SELECTEDVALUE(Parameter[Parameter Order]) = 2,IF(SELECTEDVALUE('target list C'[target list C]) = BLANK(),0,1)
)

Create a table visualization

Use the measure as the filter of the table visualization

Final output

Best regards,
Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly