Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!
Solved! Go to Solution.
Hi @gogrizz
try
New Colum =
CONCATENATEX ( TableB, IF ( TableA[WPM] >= TableB[Lower Limit] && TableA[WPM] < TableB[Upper Limit], TableB[Rank] ) )
@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]
)
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Both work - thanks team!
@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]
)
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Hi @gogrizz
try
New Colum =
CONCATENATEX ( TableB, IF ( TableA[WPM] >= TableB[Lower Limit] && TableA[WPM] < TableB[Upper Limit], TableB[Rank] ) )
User | Count |
---|---|
41 | |
27 | |
23 | |
18 | |
15 |
User | Count |
---|---|
54 | |
35 | |
19 | |
18 | |
15 |