Calculated column based on values in other columns?

[tmitton]

I have four surveys feeding into a single Power BI dataset. The four surveys all have different questions, but are related to one another which is why I pulled them into a single data table. Since all of the questions are different, each questions gets its own column and if a column is blank that just means that the corresponding Survey was not completed for that row. 

I want to create a calculated column that will identify which survey was completed for that particular row of data. In my example below, if colulmn B contains a value, I want column A to return a value of "Survey #1". If column B does not contain data, I want it to look at columns C, D, and E, and whichever one has a value, tell me which Survey was completed. 

I recreated this in excel to try and provide a visual of what I'm trying to do in Power BI. 

[Anonymous]

Hi @tmitton ,

Could you tell me if your problem has been solved?
If it is, kindly Accept it as the solution. More people will benefit from it.
Or you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file from your Onedrive for Business.

Best Regards,
Stephen Tao

[Anonymous · Accepted solution]

Hi @tmitton ,

Try this calculated column

Calculated column =
"Question#"
    & IF (
        [Qustion#1] = BLANK (),
        IF (
            [Qustion#2] = BLANK (),
            IF ( [Qustion#3] = BLANK (), IF ( [Qustion#4] = BLANK (), BLANK (), 4 ), 3 ),
            2
        ),
        1
    )

You can check more details from here.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Greg_Deckler]

@tmitton Try unpivoting your Question columns in Power Query. (B, C, D, E columns)