Assign Category to Each Value in a Column

[gogrizz]

I have a column of values (called "WPM") in Table A.

Table B has upper and lower limits with a corresponding category/rank values:

Rank	Lower Limit	Upper Limit
1	0	5
2	6	10
3	11	15
4	16	24
5	26	1000

I want to create a column in Table A that assigns the "Rank" Value to the WPM based on the value of WPM and where it falls within the 2 limits.  I could do it with a bunch of if/switch statements, but if the upper and lower limits change, it's more work to change the if/switch statements - mainly because there are 5 more columns like WPM that need the same treatment.

Please advise - thanks!

[tamerj1 · Accepted solution]

Hi @gogrizz 

try

New Colum =

CONCATENATEX ( TableB, IF ( TableA[WPM] >= TableB[Lower Limit] && TableA[WPM] < TableB[Upper Limit], TableB[Rank] ) )

[gogrizz]

Both work - thanks team!

[Fowmy · Accepted solution]

@gogrizz 

Add the following column in Table A, this is dynamic.

Rank Result = 
VAR __V = 'Table A'[Value]
RETURN
MAXX(
    FILTER(
        'Table B',
        __V <= 'Table B'[Upper Limit]  &&
        __V >= 'Table B'[Lower Limit]
    ),
    'Table B'[Rank]
) 

[tamerj1 · Accepted solution]

Hi @gogrizz 

try

New Colum =

CONCATENATEX ( TableB, IF ( TableA[WPM] >= TableB[Lower Limit] && TableA[WPM] < TableB[Upper Limit], TableB[Rank] ) )