Forum Discussion
Lookup description for code
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
1 Reply
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
