March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
Register NowGet 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
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
Solved! Go to 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!
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.
Make sure it is not a measure. You may also try CONTAINS and LOOKUPVALUE.
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
@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.
@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.
@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.
@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
Make sure it is not a measure. You may also try CONTAINS and LOOKUPVALUE.
@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
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
Check out the November 2024 Power BI update to learn about new features.
User | Count |
---|---|
121 | |
97 | |
85 | |
69 | |
61 |
User | Count |
---|---|
138 | |
116 | |
114 | |
99 | |
98 |