Forum Discussion

KristofferAJ's avatar
KristofferAJ
Helper III
1 year ago
Solved

Related column between two tables

I have a small table (ORANGE) with a 1-to-many relationship to a very large dataset (APPLE), the connection point is "city"   I would like to establish a column in APPLE indicating if the city is p...
Need Help
Show and Tell
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi, KristofferAJ 

    Use the following expression to create a calculated table:

    Table 2 = SUMMARIZE('APPLE','APPLE'[City],"ORANGE",[Measure])

    Recreate the relationship as follows:

    Since the countries in the ORANGE table do not necessarily include the countries in APPLE, blank values ​​will appear when it is put into the slicer:

    So set this column:

     

     

    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.

     

     

     

     

KristofferAJ's avatar
KristofferAJ
Helper III
1 year ago

Hi! this looks very spot on, I was trying it but got an error saying that the column 'cannot be pushed to the remote data source and cannot be used in this sceanrio' - I suspect its because its a model which I dont have rights to make a column in?

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Thank you Bibiano_Geraldo 

    Hi, KristofferAJ 

    First, your APPLE table join mode is Directquery, and secondly your ORANGE is import. You have a one-to-many relationship for them.

    I did run into the same problem as you when I created a calculated column in direct query mode using Super user's expression:

    To do this, I've created a measure as follows:

    Measure = 
VAR _apple_table = SUMMARIZE(ALLSELECTED(APPLE),'APPLE'[City],"column",
    IF(ISBLANK(LOOKUPVALUE('Table'[ORANGE],'Table'[ORANGE],'APPLE'[City])),"FALSE","TRUE")
)
VAR _current_city = SELECTEDVALUE(APPLE[City])
RETURN MAXX(FILTER(_apple_table,'APPLE'[City] = _current_city),[column])

    It works correctly with your imported Table and Direct Query connection mode tables. 

     

     

    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.

    • KristofferAJ's avatar
      KristofferAJ
      Helper III
      1 year ago

      This is great and works! - I can use it as part of a table, but not as legend or in a filter, suspect thats becasue its a measure?

      Any way that this can be made into a column instead? - when I try it gives me the "circular dependency" error

      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        Hi, KristofferAJ 

        Use the following expression to create a calculated table:

        Table 2 = SUMMARIZE('APPLE','APPLE'[City],"ORANGE",[Measure])

        Recreate the relationship as follows:

        Since the countries in the ORANGE table do not necessarily include the countries in APPLE, blank values ​​will appear when it is put into the slicer:

        So set this column:

         

         

        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.

         

         

         

         

  • Bibiano_Geraldo's avatar
    Bibiano_Geraldo
    Super User
    1 year ago

    Hi KristofferAJ ,
    The error you're encountering indicates that the data source you're working with is a DirectQuery model or resides in a database where you don't have the ability to modify or create calculated columns.

     

    You can learn more about Limitations of DirectQuery in Power BI here