Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
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.
Solved! Go to 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
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.
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
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
User | Count |
---|---|
85 | |
81 | |
64 | |
53 | |
46 |
User | Count |
---|---|
102 | |
49 | |
42 | |
39 | |
38 |