Answered by:
Hasonevalue & switch
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, "1630 ml", 2, "3150 ml", 3, "51100 ml", 4, "101200 ml", 5, ">200 ml", 6, "Others", 7 ), 9 ) The result of this formula :
The "Size Range" column is a calculated column. Does it matters?
 Edited by Bartek Wachocki Wednesday, February 10, 2016 9:39 AM
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, "1630 ml", 2, "3150 ml", 3, "51100 ml", 4, "101200 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, "1630 ml", 2, "3150 ml", 3, "51100 ml", 4, "101200 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. ThanksThursday, February 11, 2016 9:15 AM