Create column to check if ID exists in another dataset

[Arranafc19]

Hi 

I need some help with a data modelling query I have in power bi.

I have two datasets pulling from two seperate databases.

My "ChosenIDs" has one column  (ID_Number) and contains a list of particular ids which meet a set of requirements.

My second dataset "ItemInformation" contains a list of all items , some of which exist in the "ChosenIDs" dataset.

My goal is to create a column in the "ItemInformation" dataset which returns 1 or 0 depending on if the ID exists in the "ChosenIDs" dataset.

Can someone advise how this could be done ? I have thought about merging , but I would prefer to have the column to identify this . 

Thanks

[parry2k · Accepted solution]

@Arranafc19 then it is pretty straight forward, set relationship between these two tables on ID, and add the following column

Flag = 
IF ( RELATED ( ChosenId[Id] ) == BLANK(), 0, 1 )

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

[v-chuncz-msft · Accepted solution]

@Arranafc19 

Make sure it is not a measure. You may also try CONTAINS and LOOKUPVALUE.

[parry2k]

@Arranafc19 can you share sample data?  I think I understood the problem but want to be sure before I provide a solution.

[Arranafc19]

Hi @parry2k 

Here is an idea below of how what my datasets look like at the moment and how I would lik it to work.

"ChosenIDs" Dataset 

ID

457

13

435

24

546

124

"ItemInformation" Dataset

ID   |    Name      |     Price   

--------------------------------

121 |    Hyundai  |    15000

457 |    Toyota    |     7000

145 |    Ford        |     4500

99   |    BMW      |      25000

124 |    Nissan    |      3000

How I would like it to look with additional column :

ID   |    Name      |     Price     | Included ?

---------------------------------------------

121 |    Hyundai  |    15000    |     0

457 |    Toyota    |     7000     |     1

145 |    Ford        |     4500     |     0

99   |    BMW      |      25000   |     0

124 |    Nissan    |      3000     |     1

[parry2k]

@Arranafc19 Id in the chosenid is unique or there can be duplicate ids?

[Arranafc19]

@parry2kthese ids would be unique 

[parry2k · Accepted solution]

@Arranafc19 then it is pretty straight forward, set relationship between these two tables on ID, and add the following column

Flag = 
IF ( RELATED ( ChosenId[Id] ) == BLANK(), 0, 1 )

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

[Arranafc19]

Hi @parry2k 

I created the relationship between these and tried to add the filter but get the following error :

The column 'ChosenId[Id]' either doesn't exist or doesn't have a relationship to any table available in the current context.

The field is there and I added the relationship so any idea why this isn't recognising it ?

[parry2k]

@Arranafc19 can you share screen shot of relationship diagram.

[Arranafc19]

@parry2k

 

[parry2k]

@Arranafc19 so what is not working, now I'm lost, so you had flag column in the table with 0 and 1, what is not working?

[Arranafc19]

@parry2k  the flag column isnt working :

[parry2k]

@Arranafc19 you are adding this Flag as a column in ItemInformation table, correct?

[Arranafc19]

@parry2k  I am yes. This is where the flag needs to check each row to check if the id exists in the chosenid dataset 

[v-chuncz-msft · Accepted solution]

@Arranafc19 

Make sure it is not a measure. You may also try CONTAINS and LOOKUPVALUE.

[parry2k]

@Arranafc19 I'm surely missing something here, not sure why you are getting error. Can you share pbix file, remove sensitive information before sharing.

Just to confirm again, you are adding a column, correct?

[Arranafc19 · Accepted solution]

Hi @parry2k ,

The issue seems to be with the use of direct query , I have changed to import and this is working now.

Thanks for your help