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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
luisadavidson
Frequent Visitor

Add reference table

Hello!

 

I've got a dataset with a column with only values in it, essentially a code, and I want to link that to what they actually mean.

 

e.g. 1 = Red, 2 = blue, 3 = yellow etc.

 

I have another spreadsheet with column 1 = number and column 2 = colour, but I'm not sure what to do with it, or how/where to add it.

 

Please could you let me know what the best way to link these would be?

 

Thanks!

1 ACCEPTED SOLUTION

@luisadavidson add this a column NOT measure



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

6 REPLIES 6
parry2k
Super User
Super User

@luisadavidson can you share sample data how your tables looks like? Read this post to get your answer quickly.

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490



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.

Ah thanks for that link, hadn't seen that yet!

 

I’m not sure I’m really able to provide a sample as it’s sensitive data I’m dealing with.

 

Essentially I’m trying to match the below descriptions in a separate spreadsheet, with my original dataset which has a column called Ethnicity that only has the codes in them. I just want to pull in the descriptions. Does that help?

 

CODE

DESCRIPTION

1

White

2

White - British

3

White - Irish

  

 

@luisadavidson It should be straight forward, assuming the table info your provided, code is unique in it.

 

You can set relationship between this table with you other table on code and ethnicity. I'm assuming both column data type is same. When you set up the relation ship it will be 1 to many, 1 on table you shown in the post, and many on another table.

 

With this relationship, you can now access description column in your other table by writing following DAX code

 

Color = RELATED( Table[Description] )


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.

ok thank you, getting there I think.

 

Have the relationship set up:

Table 1 (appointments) - code

Table 2 (ethnicity) - code

Many to 1 - first table has lots, second only has unique values

 

When I try to add the measure though I think I'm doing it wrong.

 

I've created a measure with:

EthnicityDesc = RELATED( Ethnicity[Description] )

as i've renamed the other table as such, but it doesn't seem to like it? What am I missing? Should I be creating this measure in the new table?

@luisadavidson add this a column NOT measure



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.

Thank you SO much!

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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