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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

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
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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

October NL Carousel

Fabric Community Update - October 2024

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