cancel
Showing results 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

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.

2 REPLIES 2
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

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.

Announcements

#### 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 Monthly Update - June 2024

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

#### 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
Top Kudoed Authors