Forum Discussion
Select category based on comparing a value to multiple values in an other table
Hello everybody,
after a few days of trying and struggling I hope someone here can help me with the following issue:
In one of my tables I have a percentage, which is displayed in a decimal number.
In another table I have like 5 categories + percentages (also in decimal number format) for the beginning and ending of the specific range for the category. It looks like this:
| Category | Start of category | End of category |
| A | 0.9 | 1 |
| B | 0.5 | 0.9 |
| C | 0.25 | 0.5 |
| D | 0 | 0.25 |
| E | 0 |
I would like to compare my calculated value to the start and end values and based on the range where my calculated value falls in, select the category.
I'm using DAX for this and my tables aren't related to eachother.
I hope this case is clear enough and someone can help me.
Da_Roon , I think you are looking for Dynamic Segmentation
Dynamic Segmentation Bucketing Binning
https://community.powerbi.com/t5/Quick-Measures-Gallery/Dynamic-Segmentation-Bucketing-Binning/m-p/1387187#M626
Dynamic Segmentation, Bucketing or Binning: https://youtu.be/CuczXPj0N-kDa_Roon, I am sure that you will solve your problem using the references provided by amitchandak, but on the screenshot below you can find an alternative solution using a calculated column - check it if you like.
It has one limitation though - it won't work if you have a NULL value in the row related to the category "E" (I have tweaked this table as you can see on the screenshot). As far as I understand, you use this category for catching zeroes.
5 Replies
- amitchandak
Super User
Da_Roon , I think you are looking for Dynamic Segmentation
Dynamic Segmentation Bucketing Binning
https://community.powerbi.com/t5/Quick-Measures-Gallery/Dynamic-Segmentation-Bucketing-Binning/m-p/1387187#M626
Dynamic Segmentation, Bucketing or Binning: https://youtu.be/CuczXPj0N-kThank you very much for your reply amitchandak . I was searching for a few days, but I never would have thought to search for this specific topic.
I can also verify that this solution works.
- barritown
Solution Sage
Da_Roon, I am sure that you will solve your problem using the references provided by amitchandak, but on the screenshot below you can find an alternative solution using a calculated column - check it if you like.
It has one limitation though - it won't work if you have a NULL value in the row related to the category "E" (I have tweaked this table as you can see on the screenshot). As far as I understand, you use this category for catching zeroes.
