Lookup description for code

[TaylorN]

I have a few tables that contain a "short code" for some fields (such as status, type etc). I have another table that contains all the codes, that I would like to lookup against.

For example, one table (#1) may have a "STATUS" of "LDG", "FNL" or "OPN", which could then be looked up in the Code Ref table, to produce descriptive values like "Lodged", "Finalised", or "Open". 

Another table (#2) has a column called "TYPE", similar to the above, but with different values.

This lookup table has a column to define the "code type", such as "status", or "type", that I also need to be able to filter on. So the relationship from Table #1 to the lookup table should only return rows where the "code type" = "STATUS", and for table #2 the lookup should return where the "code type" = "TYPE".

Example lookup table:

CodeType	Code	Description
STATUS		LDG	Lodged
STATUS		OPN	Open
STATUS		FNL	Finalised
TYPE		IND	Individual
TYPE		ORG	Organisation

[Stachu]

do you want to use it in a measure or calculated column?

you could try with TREATAS
https://www.sqlbi.com/articles/propagate-filters-using-treatas-in-dax/

or do LOOKUP on a table with FILTER for a specific CodeType

or if it's guaranteed that the same code cannot be in both Type and Status then you could just do join on the [Code]

you could also guarantee that by creating columns with values like

STATUS_LDG

TYPE_IND