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. No...
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.

     

     

     

ryan_mayu's avatar
ryan_mayu
Super User
2 years ago

could you pls provide some sample data and expected output?

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.