cancel
Showing results for
Did you mean:

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

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

1 ACCEPTED SOLUTION
Helper I

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

3 REPLIES 3
Community Support

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.

Helper I

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

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:

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

Announcements

#### New forum boards available in Real-Time Intelligence.

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

#### Power BI Monthly Update - May 2024

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

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors