Hi there,
I have two tables (main table and classification code table). They are not interlinked. There is a column in the main table which contains codes. The classification code table contains the classification of code i.e. which code belongs to which category. I used the following formula to create a column in the main table to define the classification of the code in the main table:
Classification = IF(‘Main Table’[ANZSIC_CODE] IN DISTINCT(‘Classification Code’[Agriculture]),“Agriculture”, IF(‘Main Table’[ANZSIC_CODE] IN DISTINCT(‘Classification Code’[Commercial]),“Commercial”,IF(‘Main Table’[ANZSIC_CODE] IN DISTINCT(‘Classification Code’[Council]),“Council”,IF(‘Main Table’[ANZSIC_CODE] IN DISTINCT(‘Classification Code’[Industrial]),“Industrial”,IF(‘Main Table’[ANZSIC_CODE]IN DISTINCT(‘Classification Code’[Residential]),“Residential”,"")))))
The above formula worked but I found that there are quite a few codes that are classified as null or “”. When I checked those code in the classification code table, codes were present in the table, however, it was not picked up by the above formula. Could anyone help me where am I making the mistake? The file is attached here
sample.pbix (1.5 MB)
.
nice catch.