help with if formula asap

[Anonymous]

.

[Anonymous]

Hi.

Any help anyone?

[mussaenda]

hi @Anonymous ,

@Anonymous  answered your question, right?

This has been solved?

[Anonymous]

no, need help

[Anonymous]

@mussaenda 

[Anonymous]

hi, 

Maybe its possible in m query as well?

[Anonymous]

Hi, 

Not sure if its the best way to do it but you can create a new column: 

New Column = IF(Sheet1[Type] = "Car" && Sheet1[Value] <= 10; 1; IF(Sheet1[Type] = "Car" && Sheet1[Value] > 10 && Sheet1[Value] <= 20; 2; IF(Sheet1[Type] = "Car" && Sheet1[Value] > 20 && Sheet1[Value] <= 30; 3; IF(Sheet1[Type] = "Car" && Sheet1[Value] > 30 && Sheet1[Value] <= 40; 4; Sheet1[Value]))))

Hope it helps

Br

Adrian

[Anonymous]

My values are from 

[Anonymous]

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: 

NewCarValue = IF(MainTable[Type] = "Car"; RELATED(CarRangeValue[New Value]); MainTable[Value])

You also need a relationship like this:

/Adrian

[Anonymous]

tahnx

[Anonymous]

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

[Anonymous]

lets 

[mussaenda]

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:

[Anonymous]

please look at the attach image.

[Anonymous]

@mussaenda 

did ur last post get deleted? didnt get anything but did get a notificaton

[mussaenda · Accepted solution]

@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.

[mussaenda]

Hi @Anonymous ,

this can be.

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"

I duplicated your value column on power query and set hte data type as text.

Then the code I changed a little bit. Take note of the red boxes.

Take note that your column is now treated as text. this cannot be aggregated.

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] & "%")