Forum Discussion

Laiq_Rahman's avatar
Laiq_Rahman
Helper I
2 years ago
Solved

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...
Need Help
  • Laiq_Rahman's avatar
    Laiq_Rahman
    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 bucket

     

    In this way, i am able to create what i expected

Anonymous's avatar
Anonymous
Not applicable
2 years ago

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's avatar
    Laiq_Rahman
    Helper I
    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 bucket

     

    In this way, i am able to create what i expected