Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills 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.
Check out the November 2023 Power BI update to learn about new features.
Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.