Add custom column based on multiple status columns

[prita]

Hello,

I have a data table with multiple status fields, I want to create a custom column based on the values of these fields. Here is a sample of what my data looks like:

Candidate	Stage 1	Stage 2	Stage 3	Stage 4
A	Assigned	Skipped	Assigned	Assigned
B	Assigned
C	Assigned	Assigned

These columns are a part of a larger data table that is imported from a live SharePoint list. This list is tracking the progress of a test that has multiple stages. A candidate progresses to Stage 2 only if they have cleared stage 1 OR have skipped stage 1 and have been straight away assigned to Stage 2. 

As soon as a candidate name is entered to the list the stage 1 column is assigned some value, so the Stage 1 column will always have some value either "assigned" or "skipped". I want to create a custom column in Power BI that will tell me which stage a candidate is at. So if Stage 4 has no value and stage 3 has no value but stage 2 is not blank then my custom column value should say "Stage 2".

Hope I was able to explain my requirement, let me know there are questions. TIA

[ryan_mayu · Accepted solution]

@prita 

do you want to find the latest status or stage name?

Column = if(ISBLANK(Sheet17[Stage 4]),if(ISBLANK('Sheet17'[Stage 3]),if(ISBLANK(Sheet17[Stage 2]),Sheet17[Stage 1],Sheet17[Stage 2]),Sheet17[Stage 3]),Sheet17[Stage 4])

Column2 = if(ISBLANK(Sheet17[Stage 4]),if(ISBLANK('Sheet17'[Stage 3]),if(ISBLANK(Sheet17[Stage 2]),"stage1","stage2"),"stage3"),"stage4")

[prita]

@ryan_mayu  Thank you so much! It worked 😊

[prita]

Hi @ryan_mayu 

I want the stage name, thank you for this, let me apply this and get back.