Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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
Solved! Go to Solution.
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.
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
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.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
User | Count |
---|---|
87 | |
85 | |
75 | |
69 | |
65 |
User | Count |
---|---|
214 | |
124 | |
117 | |
82 | |
76 |