Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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.
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.
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.
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.
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.
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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
95 | |
80 | |
67 | |
62 |
User | Count |
---|---|
138 | |
107 | |
104 | |
82 | |
63 |