Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Arranafc19
Helper IV
Helper IV

Create column to check if ID exists in another dataset

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

3 ACCEPTED SOLUTIONS

@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!

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

View solution in original post

@Arranafc19 

 

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

 

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

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 

View solution in original post

15 REPLIES 15
parry2k
Super User
Super User

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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

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

 

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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

@parry2kthese ids would be unique 

@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!

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

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 ?

@Arranafc19 can you share screen shot of relationship diagram.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

@parry2k

 

 Capture.PNG

@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?



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

@parry2k  the flag column isnt working :

 

Capture.PNG

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

 

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

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

@Arranafc19 

 

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

 

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@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?



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

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 

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors