cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

help with if formula asap

.

1 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

2019_09_03_14_23_43_Untitled_Power_BI_Desktop.png

 

Just take note that your column now is treated as text data type. You cannot aggregate this column.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

16 REPLIES 16
Anonymous
Not applicable

Hi.

Any help anyone?

hi @Anonymous ,

 

@adny  answered your question, right?

This has been solved?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Anonymous
Not applicable

no, need help

Anonymous
Not applicable

Anonymous
Not applicable

hi, 

Maybe its possible in m query as well?

adny
Resolver I
Resolver I

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]))))
 
2019-09-02 15_33_02-Untitled - Power BI Desktop.png
 
Hope it helps
 
Br
Adrian
Anonymous
Not applicable

My values are from 

Another way to do it is to create a lookuptable with the ranges, somthing like this: 

ranges.png

(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])
 
new column.png
 
You also need a relationship like this:
2019-09-03 09_47_55-Untitled - Power BI Desktop.png
/Adrian
Anonymous
Not applicable

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

 

Anonymous
Not applicable

lets 

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:

2019_09_03_13_23_34_Untitled_Power_BI_Desktop.png

 

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Anonymous
Not applicable



please look at the attach image.

Anonymous
Not applicable

@mussaenda 

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

@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

2019_09_03_14_23_43_Untitled_Power_BI_Desktop.png

 

Just take note that your column now is treated as text data type. You cannot aggregate this column.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

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.

2019_09_03_14_23_43_Untitled_Power_BI_Desktop.png

 

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

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors