Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Laiq_Rahman
Helper I
Helper 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 RangeEnd Range
-2000-1000
-10000
01000
10002000
20003000

 

I have table b like below price.

 

ProductPrice
Product A-50
Product B50
Product C100
Product D1400
Product E2500

 

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.

 

ProductPriceBucket
Product A-50<0
Product B501000
Product C1001000
Product D1400>1000
Product E2500>1000

 

Based on the user selection, the calculated column should show the price lesser or higher. Is this doable in dax. Please help.

1 ACCEPTED 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

View solution in original post

3 REPLIES 3
Anonymous
Not applicable

Hi @Laiq_Rahman ,

 

Yes indeed, you need a measure implementation. I made simple samples and you can check the results below:

vtianyichmsft_0-1713147576820.png

vtianyichmsft_1-1713147599173.png

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

TomMartens
Super User
Super 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:

Hopefully, one of these articles will help you to tackle your challenge.

 

Regards,

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.