cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
luisadavidson
Regular 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

Accepted Solutions
Super User IV
Super User IV

Re: Add reference table

@luisadavidson add this a column NOT measure






Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

6 REPLIES 6
Super User IV
Super User IV

Re: Add reference table

@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






Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Highlighted
luisadavidson
Regular Visitor

Re: Add reference table

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

  

 

Super User IV
Super User IV

Re: Add reference table

@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] )





Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





luisadavidson
Regular Visitor

Re: Add reference table

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?

Super User IV
Super User IV

Re: Add reference table

@luisadavidson add this a column NOT measure






Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

luisadavidson
Regular Visitor

Re: Add reference table

Thank you SO much!

 

 

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors