Create a calculated column based on number range filtered

[Laiq_Rahman]

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.

[v-tianyich-msft]

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.

[Laiq_Rahman · Accepted solution]

Hi @v-tianyich-msft 

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

[TomMartens]

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