Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
ola1996
Helper I
Helper I

DAX formula for new columns with groups

Hi,

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

code column.png

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 😞

1 ACCEPTED SOLUTION
VasTg
Memorable Member
Memorable Member

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

Connect on LinkedIn

View solution in original post

6 REPLIES 6
mwegener
Most Valuable Professional
Most Valuable Professional

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.

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener works as Full Stack Power BI Engineer at BI or DIE.
His mission is clear: "Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website - podcast - Power BI Tutorials


az38
Community Champion
Community Champion

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


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn
VasTg
Memorable Member
Memorable Member

@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

Connect on LinkedIn

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

Bez tytułu.jpg

VasTg
Memorable Member
Memorable Member

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

Connect on LinkedIn

@VasTg Thanks a lot 🙂 it works correctly 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.

Top Kudoed Authors