Forum Discussion

stevedata's avatar
stevedata
Frequent Visitor
1 year ago
Solved

Connected tables problem

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

Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    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

     

     

     

     

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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