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
gogrizz
Advocate I
Advocate I

Assign Category to Each Value in a Column

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

 

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

RankLower LimitUpper Limit
105
2610
31115
41624
5261000

 

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!

2 ACCEPTED SOLUTIONS
tamerj1
Super User
Super User

Hi @gogrizz 

try

New Colum =

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

View solution in original post

Fowmy
Super User
Super User

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






Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

View solution in original post

3 REPLIES 3
gogrizz
Advocate I
Advocate I

Both work - thanks team!

Fowmy
Super User
Super User

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






Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

tamerj1
Super User
Super User

Hi @gogrizz 

try

New Colum =

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

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.

Top Solution Authors