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.

     

     

     

     

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.

     

     

     

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi, KristofferAJ 

    If you still just want to create a calculated column in the APPLE table, you can try the following expression:

    Column = 
VAR _table = {
    ("London"),
    ("Munich")
}
RETURN IF([City] IN _table,"TRUE","FALSE")

     

    You need to structure the countries in your ORANGE table in _table with each bracket representing a row.

     

     

    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.