Forum Discussion

tmitton's avatar
tmitton
Frequent Visitor
5 years ago
Solved

Calculated column based on values in other columns?

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. 

 

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    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.

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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