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?

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