Forum Discussion
help with if formula asap
Anonymous,
Not sure why it was removed.
I will re-post:
I duplicated your value column. Check this so you can see.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wck4sUtJRMjRQitWJVsrNL8kvSq5MzkkFihmbgsVKikqTs4FcI2MwNxms3hiiHqLZ2ATMCYEqNLQAc32RzTIEKokFAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Type = _t, Value = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Value", type number}}), #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "Value", "Value - Copy"), #"Changed Type1" = Table.TransformColumnTypes(#"Duplicated Column",{{"Value - Copy", type text}}) in #"Changed Type1"Then I change a little bit your code:
Column = IF('Table (4)'[Type] = "Car", SWITCH( TRUE(), 'Table (4)'[Value] <= 26, "24", 'Table (4)'[Value] <= 31, "23", 'Table (4)'[Value] <= 35, "22", 'Table (4)'[Value] <= 39, "21", 'Table (4)'[Value] <= 43, "20", 'Table (4)'[Value] <= 48, "19", 'Table (4)'[Value] <= 52, "18", 'Table (4)'[Value] <= 58, "17", 'Table (4)'[Value] <= 64, "16", 'Table (4)'[Value] <= 71, "15", 'Table (4)'[Value] <= 77, "14", 'Table (4)'[Value] <= 86, "13", 'Table (4)'[Value] <= 95, "12", 'Table (4)'[Value] <= 106, "11", 'Table (4)'[Value] >= 107, "10"), 'Table (4)'[Value - Copy] & "%")You will see the changes inside the red boxes
Just take note that your column now is treated as text data type. You cannot aggregate this column.
Another way to do it is to create a lookuptable with the ranges, somthing like this:
(I created this in excel)
And then create a new column in main table:
tahnx
Im sorry but I can't do this for you, but I'll try to explain step by step:
Step 1:
Create a new table (in excel or power bi) with an Index column 1-120 (or as far as you want the ranges to go).
In the next column you put the new value the car shoud get.Like this:
Car Value
1 31
2 31
3 30
4 30
5 30
6 30
7 29
8 29
9 29
10 29
...Step 2:
Load this excel into power bi.In relationship view, create a relationship between the index column ("Car Value" in this example) and the value column in original table.
Step 3:
Create a new column in power bi original table with DAX formula:
NewCarValue = IF(MainTable[Type] = "Car"; RELATED(CarRangeValue[New Value]); MainTable[Value])
You might have to change the table and column names to match your data.
/Adrian
lets
- mussaenda
Community Champion
hi Anonymous ,
Since you don't want to add an excel file, try this:
Column = IF('Table (4)'[Type] = "Car", SWITCH( TRUE(), 'Table (4)'[Value] <= 26, 24, 'Table (4)'[Value] <= 31, 23, 'Table (4)'[Value] <= 35, 22, 'Table (4)'[Value] <= 39, 21, 'Table (4)'[Value] <= 43, 20, 'Table (4)'[Value] <= 48, 19, 'Table (4)'[Value] <= 52, 18, 'Table (4)'[Value] <= 58, 17, 'Table (4)'[Value] <= 64, 16, 'Table (4)'[Value] <= 71, 15, 'Table (4)'[Value] <= 77, 14, 'Table (4)'[Value] <= 86, 13, 'Table (4)'[Value] <= 95, 12, 'Table (4)'[Value] <= 106, 11, 'Table (4)'[Value] >= 107, 10), 'Table (4)'[Value])Based form your provided data, here is the result: