Forum Discussion
Create a calculated column based on number range filtered
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.
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 bucketIn this way, i am able to create what i expected
3 Replies
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:
- static segmentation: Static segmentation – DAX Patterns
- dynamic segmentation: Dynamic segmentation – DAX Patterns
Hopefully, one of these articles will help you to tackle your challenge.
Regards,
Tom
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 ChangIf 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 bucketIn this way, i am able to create what i expected
