Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
113 | |
109 | |
100 | |
39 | |
31 |
User | Count |
---|---|
153 | |
122 | |
77 | |
74 | |
44 |