cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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
Microsoft

@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
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors