Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
User | Count |
---|---|
56 | |
22 | |
18 | |
16 | |
11 |
User | Count |
---|---|
85 | |
54 | |
39 | |
21 | |
18 |