locked
Is this a case for IF or SWITCH or something else. RRS feed

  • Question

  • Hello;

    I have a table in my database that has 4 columns that can either have a True or False value (0 or 1) – Let’s call these columns A, B, C and D respectively because I am so creative. PowerPivot calls these TRUE/FALSE Data Types.


    I want to populate a 5th column in the table that contains the names of the 4 columns if their value is True.


    For example, if all 4 are true, I want my new column to contain A, B, C, D. If only A, C and D are true, then my column should contain A, C, D or if A and D are true, then it would contain A, D. (The comma and space are mandatory)

    Also the last comma should be removed.


    Is there a way achieve this or is this just a pipe dream?


    Thanks  in advance for the help.


    Cheers


    Thanks Ron...

    Monday, August 8, 2016 3:57 PM

Answers

  • Hello Ron,

    Create an invisible calculated column with this expression:

    = IF(Table1[A], "A, ", "") & IF(Table1[B], "B, ", "") & If(Table1[C], "C, ", "") & IF(Table1[D], "D, ", "")

    The create a second calculated column with

    = IF (LEN(Table1[tempResult]) > 0, LEFT(Table1[tempResult], Len(Table1[tempResult]) - 2), "")

    That should give you the required result, like


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Marked as answer by RonInOttawa Monday, August 8, 2016 5:46 PM
    Monday, August 8, 2016 5:28 PM

All replies

  • Hello Ron,

    Create an invisible calculated column with this expression:

    = IF(Table1[A], "A, ", "") & IF(Table1[B], "B, ", "") & If(Table1[C], "C, ", "") & IF(Table1[D], "D, ", "")

    The create a second calculated column with

    = IF (LEN(Table1[tempResult]) > 0, LEFT(Table1[tempResult], Len(Table1[tempResult]) - 2), "")

    That should give you the required result, like


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Marked as answer by RonInOttawa Monday, August 8, 2016 5:46 PM
    Monday, August 8, 2016 5:28 PM
  • OMG Olaf, that is so wonderful. I don't know how to thank you. I've been struggling with this for several hours. It makes so much sense. I was able to do the same thing in T-SQL, but couldn't get to do it PowerPivot.

    I so appreciate this.


    Thanks Ron...

    Monday, August 8, 2016 5:46 PM