cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
DeanM Frequent Visitor
Frequent Visitor

new column to enter data in a range

Hi,

New to BI and I am trying to create a new column for sale dates into 4 categories (0-30, 31-60, 61-90, 91+).  I have a column of sale days.

 

1 ACCEPTED SOLUTION

Accepted Solutions
rajendran Super Contributor
Super Contributor

Re: new column to enter data in a range

Hi

 

Try this

Bucket Range = SWITCH( TRUE(), 
    Table1[Value]<=30,"0-30",
    Table1[Value]<=60,"31-60",
    Table1[Value]<=90,"61-90","91+")


Thanks
Raj

 

7 REPLIES 7
Super User
Super User

Re: new column to enter data in a range

Try using "Conditional Column" under Power Query Editor, where you can define your ranges using IF Clause.



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

Proud to be a Datanaut !





Highlighted
rajendran Super Contributor
Super Contributor

Re: new column to enter data in a range

Conditional column is the way to do in power Query, as suggested by @PattemManohar.

 

Alternatively, You can do this as calculated column in Power BI model as well. Go to Modelling Tab -> New column-> Write your DAX formula here. ( IF (<con1> , "0-30",<Con2>,"31-60" , etc)

 

Thanks

Raj

DeanM Frequent Visitor
Frequent Visitor

Re: new column to enter data in a range

In Power BI I have a column sales days ranging from 0-500.  I need a formula for the new column sales profile 0-30, 31-60, 61-90, 90+.  Keep getting errors.

 

rajendran Super Contributor
Super Contributor

Re: new column to enter data in a range

What formula you use? Show your sample data and formula you are trying.

 

Thanks

Raj

DeanM Frequent Visitor
Frequent Visitor

Re: new column to enter data in a range

Sample:

I have a column in my master table with sale days but I want to create a new column for sales profile

Sales Days | Sales Profile

7                 0-30

55               31-60

95               91+

18               0-30

65               61-90

 

I have started with: Sales day profile = If([Sales Day]<30,"0-30",0)  before I start adding additional criteria.

Thanks for your help.

rajendran Super Contributor
Super Contributor

Re: new column to enter data in a range

Hi

 

Try this

Bucket Range = SWITCH( TRUE(), 
    Table1[Value]<=30,"0-30",
    Table1[Value]<=60,"31-60",
    Table1[Value]<=90,"61-90","91+")


Thanks
Raj

 

DeanM Frequent Visitor
Frequent Visitor

Re: new column to enter data in a range

Raj, Thanks so much. It works.

Helpful resources

Announcements
Community Highlights

Community Highlights

Find out what's new in the Power BI Community!

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 308 members 3,717 guests
Please welcome our newest community members: