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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
yaronch7
Regular Visitor

How to create dynamic data bin using Dax

Hi 

 

My data structure is as follow :

order id     sale_price

1                  $35

2                   $22

3                  $7

 

I am looking for a way to create calculate column  with Dax to creeate dynamic sale price range and to get a table like below

 

order id     new calculate field

1                  $30-40

2                   $20-30

3                  $5-10

 

Thanks

1 ACCEPTED SOLUTION
Anonymous
Not applicable

The following should do the sort of thing you want:

= SWITCH(TRUE(),
Table1[sale_price] < 5, "$<5",
AND(Table1[sale_price]>=5, Table1[sale_price]<10), "$05-10",
AND(Table1[sale_price]>=10, Table1[sale_price]<20), "$10-20",
AND(Table1[sale_price]>=20, Table1[sale_price]<30), "$20-30",
AND(Table1[sale_price]>=30, Table1[sale_price]<40), "$30-40",
"$40+")

But, for a more robust solution that is maintainable, sortable etc., see posts such as https://blogs.msdn.microsoft.com/analysisservices/2014/06/05/bucketing-values-in-dax/ and the Marco Russo links in the comments at the end of tha tblog.

View solution in original post

2 REPLIES 2
v-haibl-msft
Microsoft Employee
Microsoft Employee

@yaronch7

 

The DAX solution provided by Steve_Wheeler should work. We can also do it with power query in Query Editor.

 

let
    Source = Excel.Workbook(File.Contents("C:\How to create dynamic data bin using Dax.xlsx"), null, true),
    Table1_Table = Source{[Item="Table1",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Table1_Table,{{"order id", Int64.Type}, {"sale_price", Int64.Type}}),
    #"Added Conditional Column" = Table.AddColumn(#"Changed Type", "Custom", each if [sale_price] > 5 and [sale_price] < 10 then "$5-10" else if [sale_price] > 20 and [sale_price] < 30 then "$20-30" else if [sale_price] > 30 and [sale_price] < 40 then "$30-40" else null)
in
    #"Added Conditional Column"

How to create dynamic data bin using Dax_1.jpg

 

Best Regards,

Herbert

Anonymous
Not applicable

The following should do the sort of thing you want:

= SWITCH(TRUE(),
Table1[sale_price] < 5, "$<5",
AND(Table1[sale_price]>=5, Table1[sale_price]<10), "$05-10",
AND(Table1[sale_price]>=10, Table1[sale_price]<20), "$10-20",
AND(Table1[sale_price]>=20, Table1[sale_price]<30), "$20-30",
AND(Table1[sale_price]>=30, Table1[sale_price]<40), "$30-40",
"$40+")

But, for a more robust solution that is maintainable, sortable etc., see posts such as https://blogs.msdn.microsoft.com/analysisservices/2014/06/05/bucketing-values-in-dax/ and the Marco Russo links in the comments at the end of tha tblog.

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors