Related column between two tables

[KristofferAJ]

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 present in ORANGE, with a statement of false or true depending if the city is present in both tables..

I have not access to the datamodel in the back so I need to handle this through inserting a column

Perhaps RELATED? LOOKUPVALUE? RELATEDTABLE?

[KristofferAJ]

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?

[v-jianpeng-msft]

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]

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

[v-jianpeng-msft]

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.

[v-jianpeng-msft · Accepted solution]

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]

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

[Bibiano_Geraldo]

Hi @KristofferAJ ,

You can use LOOKUPVALUE function to achieve your goal:
Go to the APPLE table in Power BI Desktop and use the following DAX formula for the new column:

CityExistsInOrange = 
IF(
    ISBLANK(
        LOOKUPVALUE(
            ORANGE[city], 
            ORANGE[city], 
            APPLE[city]
        )
    ), 
    FALSE, 
    TRUE
)