locked
Hasonevalue & switch RRS feed

  • Question

  • Hi

    Does anybody know why my calculated column formula doesn't show the proper values?

    (It always returns "9")

    Position =
    IF (
        HASONEVALUE ( 'Size Range'[Size Range] ),
        SWITCH (
            VALUES ( 'Size Range'[Size Range] ),
            "<15 ml", 1,
            "16-30 ml", 2,
            "31-50 ml", 3,
            "51-100 ml", 4,
            "101-200 ml", 5,
            ">200 ml", 6,
            "Others", 7
        ),
        9
    )

    The result of this formula :

    The "Size Range" column is a calculated column. Does it matters?




    Wednesday, February 10, 2016 9:35 AM

Answers

  • Calculated Columns are evaluated in a row context. VALUES() is evaluated in a filter context. Your IF() test always returns false. Remove VALUES() from your column definition completely. You can also eliminate the IF(), as there can never be more than one value for a field in a row context:

    Position =
    SWITCH (
        'Size Range'[Size Range],
        "<15 ml", 1,
        "16-30 ml", 2,
        "31-50 ml", 3,
        "51-100 ml", 4,
        "101-200 ml", 5,
        ">200 ml", 6,
        "Others", 7
        ,9
    )


    Check out the Power BI User Group of Philadelphia.
    Our next meeting is April 7 in Malvern, PA.


    • Edited by greggyb Wednesday, February 10, 2016 4:55 PM
    • Marked as answer by Bartek Wachocki Thursday, February 11, 2016 9:15 AM
    Wednesday, February 10, 2016 4:54 PM

All replies

  • Calculated Columns are evaluated in a row context. VALUES() is evaluated in a filter context. Your IF() test always returns false. Remove VALUES() from your column definition completely. You can also eliminate the IF(), as there can never be more than one value for a field in a row context:

    Position =
    SWITCH (
        'Size Range'[Size Range],
        "<15 ml", 1,
        "16-30 ml", 2,
        "31-50 ml", 3,
        "51-100 ml", 4,
        "101-200 ml", 5,
        ">200 ml", 6,
        "Others", 7
        ,9
    )


    Check out the Power BI User Group of Philadelphia.
    Our next meeting is April 7 in Malvern, PA.


    • Edited by greggyb Wednesday, February 10, 2016 4:55 PM
    • Marked as answer by Bartek Wachocki Thursday, February 11, 2016 9:15 AM
    Wednesday, February 10, 2016 4:54 PM
  • Ya. Right. Thanks
    Thursday, February 11, 2016 9:15 AM