Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hi,
I have table A with start number and end number like below. I also have a drop down slicer on "End Range" for user to choose multiple options from the below table.
Start Range | End Range |
-2000 | -1000 |
-1000 | 0 |
0 | 1000 |
1000 | 2000 |
2000 | 3000 |
I have table b like below price.
Product | Price |
Product A | -50 |
Product B | 50 |
Product C | 100 |
Product D | 1400 |
Product E | 2500 |
I would like to create a column on table b based on user selection. Eg: If user chooses "0" and "1000" then the calculated column for table b should be populated like below.
Product | Price | Bucket |
Product A | -50 | <0 |
Product B | 50 | 1000 |
Product C | 100 | 1000 |
Product D | 1400 | >1000 |
Product E | 2500 | >1000 |
Based on the user selection, the calculated column should show the price lesser or higher. Is this doable in dax. Please help.
Solved! Go to Solution.
Hi @Anonymous
Thanks for helping me. It helped me a lot. I've created a parameter table with ranges then created a measure like below.
minrange =
Var param = MIN(Parameter[Parameter])
Var countfirstrow = COUNTX(WINDOW(-1, REL, 0, REL, SUMMARIZE(ALLSELECTED(Parameter), Parameter[Parameter]), ORDERBY(Parameter[Parameter], ASC)), Parameter[Parameter])
RETURN If(countfirstrow <=1, MINX(ALL(Parameter), Parameter[Start_range]),
MINX(WINDOW(-1, REL, 0, REL, SUMMARIZE(ALLSELECTED(Parameter), Parameter[Parameter]), ORDERBY(Parameter[Parameter], ASC)), Parameter[Parameter]))
Then using the above measure and created another dax below which works as expected.
buckettest v1 =
VAr minprice = [minrange]
var maxprice = MAX(Parameter[Parameter])
var bucket = sumx(FILTER(Table B, Table B[Price] >= minprice && Table B[Price] < maxprice
), Table B[Price])
RETURN bucket
In this way, i am able to create what i expected
Hi @Laiq_Rahman ,
Yes indeed, you need a measure implementation. I made simple samples and you can check the results below:
Measure 2 = var _min = MIN('Table 2'[End Range])
var _max =MAX('Table 2'[End Range])
var _t =SELECTEDVALUE('Table (2)'[Price])
RETURN SWITCH(TRUE(),
_t<_min,"<"&_min,
_t>_min&&_t<_max,_max,
_t>_max,">"&_max,"default")
An attachment for your reference. Hope it helps!
Best regards,
Community Support Team_ Scott Chang
If this post helps then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous
Thanks for helping me. It helped me a lot. I've created a parameter table with ranges then created a measure like below.
minrange =
Var param = MIN(Parameter[Parameter])
Var countfirstrow = COUNTX(WINDOW(-1, REL, 0, REL, SUMMARIZE(ALLSELECTED(Parameter), Parameter[Parameter]), ORDERBY(Parameter[Parameter], ASC)), Parameter[Parameter])
RETURN If(countfirstrow <=1, MINX(ALL(Parameter), Parameter[Start_range]),
MINX(WINDOW(-1, REL, 0, REL, SUMMARIZE(ALLSELECTED(Parameter), Parameter[Parameter]), ORDERBY(Parameter[Parameter], ASC)), Parameter[Parameter]))
Then using the above measure and created another dax below which works as expected.
buckettest v1 =
VAr minprice = [minrange]
var maxprice = MAX(Parameter[Parameter])
var bucket = sumx(FILTER(Table B, Table B[Price] >= minprice && Table B[Price] < maxprice
), Table B[Price])
RETURN bucket
In this way, i am able to create what i expected
Hey @Laiq_Rahman ,
a calculated column will not be updated when a user interacts with the data, e.g., by changing a slicer selection. For this reason you need a measure.
I recommend reading these two articles by the SQL BI team:
Hopefully, one of these articles will help you to tackle your challenge.
Regards,
Tom
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
72 | |
68 | |
34 | |
27 | |
26 |
User | Count |
---|---|
98 | |
96 | |
60 | |
44 | |
40 |