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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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 @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

View solution in original post

3 REPLIES 3
v-tianyich-msft
Community Support
Community Support

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 @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
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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.