Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
I'd like to find a solution in DAX to create multiple condition to round the prices of a column depending a range of price.
For example:
Price range | Round Formula |
0-1 | ROUND(1) (to the decimal) |
>1-10 | ROUND(0) (to the unit) |
>10-100 | ROUND(0) (to the unit) |
>100-1000 | MROUND(5) |
>1000-10'000 | MROUND(50) |
>10'000-100'000 | MROUND(100) |
>100'000-1'000'000 | MROUND(1000) |
Thanks so much for your kind help.
Jean-Yves
Solved! Go to Solution.
Hu Jean-Yus
Your answer =
VAR myvalue = SELECTVALUE(youtable[yourcolumn])
RETURN
SWITCH(TRUE(),
// 0-1 ROUND(1) (to the decimal)
myvalue <= 1, ROUND(myvalue,1),
// >1-10 ROUND(0) (to the unit)
myvalue <= 10, ROUND(myvalue,0),
// >10-100 ROUND(0) (to the unit)
myvalue <= 100, ROUND(myvalue,0),
// >100-1000 MROUND(5)
myvalue <= 1000, ROUND((myvalue,5),
// >1000-10'000 MROUND(50)
myvalue <= 10000, ROUND((myvalue,50),
// >10'000-100'000 MROUND(100)
myvalue <= 100000, ROUND((myvalue,100),
// >100'000-1'000'000 MROUND(1000)
myvalue <= 1000000, ROUND((myvalue,1000),
myvalue
)
Please click thumbs up and accept as solution. Thank you.
You could impove performance by removing >1-10 became it is the same as >10-100 ROUND(0).
Alsonote values over 1000000, will not be rounded
I helped you quickly, so please help me with kudos.
Please click the thumbs up and acccept solution buttons. Thank you ! 😎
Hu Jean-Yus
Your answer =
VAR myvalue = SELECTVALUE(youtable[yourcolumn])
RETURN
SWITCH(TRUE(),
// 0-1 ROUND(1) (to the decimal)
myvalue <= 1, ROUND(myvalue,1),
// >1-10 ROUND(0) (to the unit)
myvalue <= 10, ROUND(myvalue,0),
// >10-100 ROUND(0) (to the unit)
myvalue <= 100, ROUND(myvalue,0),
// >100-1000 MROUND(5)
myvalue <= 1000, ROUND((myvalue,5),
// >1000-10'000 MROUND(50)
myvalue <= 10000, ROUND((myvalue,50),
// >10'000-100'000 MROUND(100)
myvalue <= 100000, ROUND((myvalue,100),
// >100'000-1'000'000 MROUND(1000)
myvalue <= 1000000, ROUND((myvalue,1000),
myvalue
)
Please click thumbs up and accept as solution. Thank you.
You could impove performance by removing >1-10 became it is the same as >10-100 ROUND(0).
Alsonote values over 1000000, will not be rounded
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
41 | |
19 | |
19 | |
15 | |
15 |
User | Count |
---|---|
49 | |
26 | |
22 | |
17 | |
16 |