Calculated Column help

[Anonymous]

I have a table called 'All Pos' which has a many to one relationship with another table called 'Pay BF List'

The All Pos table I has a key column called [Position #] and another column called [Vacancy]

The Pay BF List table also has a key column called [Position #] and another called [Pay BF]  

Vacancy column contains the either a status of 'Encumbered' or 'Vacant'

Pay BF column contains the either a status of 'Transfer In' or 'Transfer Out'

I am trying to create a calculated column in the All Pos table that will verify if there is an entry in the 'Pay BF List' that has the same [Position #] if so it would return one of 4 results:

Encumbered + Transfer In = Encumbered

Vacant + Transfer In = Encumbered

Encumbered + Transfer Out = Vacant

Vacant + Transfer Out = Vacant

I hope this makes sense, I am reading a DAX book but I am only 80 pages in 700 to go!

[v-jingzhang · Accepted solution]

Hi @Anonymous 

Assume you already create a relationship between these two tables on Pos columns, you can try below codes to create the columns.

Column = IF(RELATED(TBLPayBF[PAY BF])="Transfer In","Encumbered",IF(RELATED(TBLPayBF[PAY BF])="Transfer Out","Vacant"))

Or

Column 2 = SWITCH(RELATED(TBLPayBF[PAY BF]),"Transfer In","Encumbered","Transfer Out","Vacant")

Kindly let me know if this helps.
Community Support Team _ Jing Zhang
If this post helps, please consider Accept it as the solution to help other members find it.

[parry2k]

@Anonymous it will be much easier if throw the data in an excel file and also the expected output, It will much quicker to put the solution together. Share the file using one drive/google drive.

[Anonymous]

https://drive.google.com/file/d/1wNCaxHWdJupJLiEifnkwxT_kQuSnnJJu/view?usp=sharing 

Tks for the reply parry2k.

I would like to create a new column that would update column B's information on Vacancy status.

If a position # in A is found in D, then return one of the following:

If B = Encumbered and E = Transfer In, then return value "Encumbered" in new created column.

If B = Vacant and E = Transfer In, then return value "Encumbered" in new created column.

If B = Encumbered and E = Transfer Out, then return value "Vacant" in new created column.

If B = Vacant and E = Transfer Out, then return value "Vacant" in new created column.

Hope this helps, I look forward to your reply.