Forum Discussion
Fall a value in a Range
Hello,
Hope you are all doing great.
I have two tables in powerbi Desktop:
Table1 contains a column DueDays that has multiple integers in it from -500 to 1000.
Table2 has 4 columns ID, MIN, MAX and Bracket.
I want to create a measure and use it as a column in Grid Table that will return the Bucket against each row in a table for example if a value=65 then it will fall in 60-90 bucket and the buckets should be sorted in the same order.
Kindly refer to the following image for more details.
Regards,
Table 2
Hi Anonymous ,
Try this:
Measure = VAR _CurrentDueDays = MAX ( Table1[DueDays] ) VAR _MIN = CALCULATE ( MAX ( Table2[Min] ), Table2[Min] <= _CurrentDueDays ) VAR _MAX = CALCULATE ( MIN ( Table2[Max] ), Table2[Max] >= _CurrentDueDays ) VAR _Bracket = CALCULATE ( MAX ( Table2[Bracket] ), Table2[Min] = _MIN, Table2[Max] = _MAX ) RETURN IF ( _CurrentDueDays >= _MIN && _CurrentDueDays <= _MAX, _Bracket )Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
Anonymous
Column = VAR __range = CALCULATE ( MAX ( 'Table'[Id] ) ) RETURN CALCULATE ( MAX ( Range[Range] ), __range >= Range[Min], __range <= Range[Max] )Check my latest blog post Year-2020, Pandemic, Power BI and Beyond to get a summary of my favourite Power BI feature releases in 2020
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
parry2k Thank you for your response.
I created the following calculated column:
Column2 =VAR __range = CALCULATE ( MAX ( 'Table'[ID] ) )RETURNCALCULATE ( MAX ( 'Table'[Bracket] ), __range >= 'Table'[Min], __range <= 'Table'[Max])But it only gets the first bracket, In this case 0-30. The column should get all the brackets.parry2k and it should compare the DueDays value column coming in the other table e.g. If DueDays=65 then it should fall into 60-90 Bracket.
Hi Anonymous ,
Try this:
Measure = VAR _CurrentDueDays = MAX ( Table1[DueDays] ) VAR _MIN = CALCULATE ( MAX ( Table2[Min] ), Table2[Min] <= _CurrentDueDays ) VAR _MAX = CALCULATE ( MIN ( Table2[Max] ), Table2[Max] >= _CurrentDueDays ) VAR _Bracket = CALCULATE ( MAX ( Table2[Bracket] ), Table2[Min] = _MIN, Table2[Max] = _MAX ) RETURN IF ( _CurrentDueDays >= _MIN && _CurrentDueDays <= _MAX, _Bracket )Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
