Forum Discussion
Laiq_Rahman
2 years agoHelper I
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 Ran...
- 2 years ago
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
TomMartens
2 years agoSuper User
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