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

I tried all three steps that you suggested but I'm still getting the same error.

PaulDBrown's avatar
PaulDBrown
Community Champion
5 years ago

zubairs 

Did you create the dimension table using DAX or is it imported/created in Power Query?

  • zubairs's avatar
    zubairs
    Helper I
    5 years ago

    It is imported through power query.

    • PaulDBrown's avatar
      PaulDBrown
      Community Champion
      5 years ago

      zubairs 
      Did you filter out the duplicates and blank rows in Power Query?

       

      • zubairs's avatar
        zubairs
        Helper I
        5 years ago

        Yes it is working now, although there's another issue. Now it has created an inactive relationship with one of the tables, due to which I'm not being able to create visual in BI.