topic Re: DAX formula for new columns with groups in DAX Commands and Tips

DAX formula for new columns with groups

ola1996 — Fri, 24 Jan 2020 21:50:14 GMT

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 😞

Re: DAX formula for new columns with groups

VasTg — Fri, 24 Jan 2020 22:18:27 GMT

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-another/m-p/910038

If this helps, mark it as a solution

Kudos are nice too

Re: DAX formula for new columns with groups

az38 — Sat, 25 Jan 2020 08:52:21 GMT

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

Re: DAX formula for new columns with groups

mwegener — Sun, 26 Jan 2020 17:38:11 GMT

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-pane

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.

Re: DAX formula for new columns with groups

ola1996 — Mon, 27 Jan 2020 07:23:01 GMT

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

Re: DAX formula for new columns with groups

VasTg — Mon, 27 Jan 2020 14:52:48 GMT

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.

Re: DAX formula for new columns with groups

ola1996 — Tue, 28 Jan 2020 13:33:07 GMT

VasTg Thanks a lot 🙂 it works correctly