Add reference table

[luisadavidson]

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!

[parry2k]

@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

[luisadavidson]

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

[parry2k]

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

[luisadavidson]

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?

[parry2k · Accepted solution]

@luisadavidson add this a column NOT measure

[luisadavidson]

Thank you SO much!