Forum Discussion

yaronch7's avatar
yaronch7
Regular Visitor
9 years ago
Solved

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 D...
Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    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.

v-haibl-msft's avatar
v-haibl-msft
Microsoft Employee
9 years ago

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"

 

Best Regards,

Herbert