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.

     

     

     

     

Bibiano_Geraldo's avatar
Bibiano_Geraldo
Super User
1 year ago

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
)