Proper way to deal with a binned lookup?

[Penguin236]

I have a column in my main table with a wide range of numeric values. I also have a lookup table that maps ranges of that column to some value, effectively defining bins. What is the proper way to associate the numeric values in my main table to the bins in the lookup?

Example:

Lookup table:

A Min            A Max           Bin

0                   50                   b1

51                 80                   b2

81                100                  b3

101                                      b4

The main table would then contain a column A with many different numeric values.

Desired main table (how do I create the "binned A" column?)

A           binned A

20             b1

42             b1

87             b3

105           b4

Thanks for the help!

[Jihwan_Kim · Accepted solution]

Hi, @Penguin236 

Please check the below Calculated Column and the link down below, that is the sample pbix file.

It is for the Column Creation, not a calculated measure.

Bin Column =

CALCULATE (

SELECTEDVALUE ( Bin[Bin] ),

FILTER ( Bin, Data[A] >= Bin[Min] && Data[A] <= Bin[Max] )

)

https://www.dropbox.com/s/zrd051stzo5gon1/penguin236.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

[Penguin236]

That worked very well, thank you! Just one question, how can I deal with the max bin? E.g. in my example lookup, everything from 101+ should get put in b4, but your code leaves those values blank. Do you know how I can do this?

[Penguin236]

Nevermind, I was able to make it work by adding an IF to check if the max is blank before comparing.

I.e.

Bin Column =

CALCULATE (

SELECTEDVALUE ( Bin[Bin] ),

FILTER ( Bin, Data[A] >= Bin[Min] && IF( ISBLANK( Bin[Max] ), True(), Data[A] <= Bin[Max] ) )

)

Using this, the code will only run the max check if Bin[Max] has a value. If it's blank, it will mark it as true no matter what (logically, because all values should be considered less than the blank).

Something similar can probably be done for a blank min, although I didn't test that.

[Jihwan_Kim]

Hi, 

Thank you for your feedback.

Terribly sorry that I quite do not understand your last question.

Please explain a bit more.

Thank you.

[Penguin236]

No problem! I was referring to that last row in my lookup table (bin b4) which doesn't have a max value assigned to it (meaning that it's unlimited). For example, your original code put an A value of 154 in a blank bin rather than bin b4. I fixed this by adding an IF to check for a blank max value in the lookup table.