Select category based on comparing a value to multiple values in an other table

[Da_Roon]

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.

[amitchandak · Accepted solution]

@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/1...

Dynamic Segmentation, Bucketing or Binning: https://youtu.be/CuczXPj0N-k

[barritown · Accepted solution]

@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.  

[Da_Roon]

Hey @barritown . Thank you for this solution. I can verrify that it worked splendidly !

[amitchandak · Accepted solution]

@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/1...

Dynamic Segmentation, Bucketing or Binning: https://youtu.be/CuczXPj0N-k

[Da_Roon]

I can also verify that this solution works.

[Da_Roon]

Thank 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.