DAX formula for new columns with groups

[ola1996]

Hi,

I need help a lot. I have one column in my database, which consist of numbers and text: 

I want to create new 2 columns which grouped this values by tens and hundreds, e.g. for 122 is in groupe 100 and 120. 

What I done by now: 

1. I divided the column to keep the number without text

2. I try to create calculated column, like that 

HundredsRange =
IF (
[Code — kopia.2] >= 100
&& [Code — kopia.2]<=199,
"100";
IF (
[Code — kopia.2] >= 200
&& [Code — kopia.2] <= 299,
"200";
IF (
[Code — kopia.2] >= 300
&& [Code — kopia.2] <= 399,
"300";
IF ( [Code — kopia.2] >= 400 && [Code — kopia.2] <= 499,
"400"; "NotExist" )
)
)
)

BUT IT DOESN't WORK. 

Can you help me? I need a piece of advice 😞

[mwegener]

Hi @ola1996 ,

I would recommend Power Query for data preparation.

Take a look at "Add Column From Examples"

https://docs.microsoft.com/en-us/power-bi/desktop-add-column-from-example#add-column-from-examples-p...

Regards,

Marcus

Dortmund - Germany
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.

[az38]

Hi @ola1996 

first, you are using different delimiters ";" and "," in your statement.  it depends on your localization but it should only commas or semicolon

do not hesitate to give a kudo to useful posts and mark solutions as solution

[VasTg]

@ola1996 

What was the error message? Are the column in correct datatype?

Your expected out is not clear with the statment "e.g. for 122 is in groupe 100 and 120. "

Do you want to group them based on the range?

Refer to this post..(Query Editor- M query)

https://community.powerbi.com/t5/Desktop/Custom-Column-New-column-to-categorize-value-as-one-or-anot...

If this helps, mark it as a solution

Kudos are nice too

[ola1996]

@VasTg Yes, I wolud like to group them base on a range, and create new columns like below:

[VasTg · Accepted solution]

@ola1996 

Refer to the link in my previous post as it solves the exact problem. Basically you have to create a custom column and give your conditions in query editor.

If it helps, mark it as a solution. 

Kudos are nice too.

[ola1996]

@VasTg Thanks a lot 🙂 it works correctly