Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Penguin236
Frequent Visitor

Proper way to deal with a binned lookup?

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!

1 ACCEPTED SOLUTION
Jihwan_Kim
Super User
Super User

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] )
)
 
 
 

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.

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


Go to My LinkedIn Page


View solution in original post

5 REPLIES 5
Jihwan_Kim
Super User
Super User

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] )
)
 
 
 

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.

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


Go to My LinkedIn Page


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?

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.

Hi, 

Thank you for your feedback.

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

Please explain a bit more.

Thank you.

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


Go to My LinkedIn Page


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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.