Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Cannot create active relationship

Hi there,   It's found I cannot create direct relationship on the new added table. From table "Queries....": Country to table "General_Performance..." Country     When I try to change the...
Need Help
  • PaulDBrown's avatar
    PaulDBrown
    5 years ago

    Anonymous 

    The reason you are getting this message is because both tables have a relationship with the calendar table, and the new filter would create a conflict since it would also indeirectly filter the date field.

    The way to solve this is to create a new dimension table  containing unique values for country from both tables, and then joining this Country Dimension Table with both your tables in 1:* relationships.

    The process is simple. Create a new table in the riboon and type in:

     

    Dimension Country =
VAR Table1 =
    DISTINCT ( Queries_byPage_byDate_byGeo[Country] )
VAR Table2 =
    DISTINCT ( General_Performance_By_Geo[Country] )
RETURN
    DISTINCT ( UNION ( Table1, Table2 ) )

     

     

zubairs's avatar
zubairs
Helper I
5 years ago

Hi Guys,

 

I am having a similar issue as I'm trying to create a relationship by position. I created a unique table with no duplicate positions but I'm still getting an error:

 

PaulDBrown's avatar
PaulDBrown
Community Champion
5 years ago

zubairs 

You probably have blank as a value hidden away in the dimension table (try sorting the column ascending and then descending and see if the blank turns up).
Blank values in the dimension table makes the relationship many-to-many, so you need to get rid of it by filtering it out in Power Query.