Forum Discussion

Ramkiyanala's avatar
Ramkiyanala
New Member
2 years ago
Solved

Power bi challenge

We have two employee tables which have no relation between them. There is a common column country in two tables
In report I have one visual from first table and another visual from second table.
Now challenge is how can we interact both visuals with one slicer from either of tables? 

Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    2 years ago

    Your solution is worked ryan_mayu , Here are the results of my tests:

    Hi, Ramkiyanala 

    I have created a simple example data where there are two tables, both of which have country columns:

    Table1:

    Table2:

    I first created a table of calculations using the following DAX expression:

    Country Table = 
VAR _table1_country = SUMMARIZE('Table1','Table1'[Country])
VAR _table2_country = SUMMARIZE('Table2','Table2'[Country])
RETURN DISTINCT(
    UNION(_table1_country,_table2_country)
)

    Use this calculated table to create a 1-to-many relationship with the COUNTRY column of the other two tables:

    The visual objects that you mentioned as coming from two separate tables are created in the report:

    Create a slicer using the country column of the calculation table:

    The results are as follows:

    I have provided the PBIX file used in this instance below.

     

     

    Best Regards

    Jianpeng Li

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

     

     

     

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Your solution is worked ryan_mayu , Here are the results of my tests:

    Hi, Ramkiyanala 

    I have created a simple example data where there are two tables, both of which have country columns:

    Table1:

    Table2:

    I first created a table of calculations using the following DAX expression:

    Country Table = 
VAR _table1_country = SUMMARIZE('Table1','Table1'[Country])
VAR _table2_country = SUMMARIZE('Table2','Table2'[Country])
RETURN DISTINCT(
    UNION(_table1_country,_table2_country)
)

    Use this calculated table to create a 1-to-many relationship with the COUNTRY column of the other two tables:

    The visual objects that you mentioned as coming from two separate tables are created in the report:

    Create a slicer using the country column of the calculation table:

    The results are as follows:

    I have provided the PBIX file used in this instance below.

     

     

    Best Regards

    Jianpeng Li

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

     

     

     

    • Ramkiyanala's avatar
      Ramkiyanala
      New Member
      2 years ago

      Unfortunately, I have no sample data for this. It was asked in interview. 

      I answered as we need to create relationship between those tables as already common column is existing. So, building relation between table helps in solving the challenge. 

       

      But I don't know whether I am correct or wrong. 

      • ryan_mayu's avatar
        ryan_mayu
        Super User
        2 years ago

        I think next time you can ask the interviewer what the solution is.

        In my opinion, there must be duplicated in country column for each table. If you create the relationship between two tables, that will be a many to many relationship. That is not recommended.

        If I meet this scenario, I will create a dim country table and use that table to filter two visuals.