Forum Discussion
Find a code in a string with the highest rank
Hi,
I have a lookup table with a list of codes and a rank assigned against each code.
The lookup table looks as follows:
| Code | Rank |
| DI0001 | 1 |
| DI0002 | 1 |
| DI0003 | 2 |
| DI0004 | 2 |
| DI0005 | 3 |
| DI0006 | 3 |
| DI0007 | 3 |
| DI0008 | 4 |
| DI0009 | 4 |
| DI0010 | 4 |
I also have a data table where there is a srtring of codes assigned to parts.
Please see below:
| Part # | Codes |
| Part 1 | DI0001 DI0002 DI0005 |
| Part 2 | DI0001 DI0002 DI0006 |
| Part 3 | DI0001 |
| Part 4 | DI0006 |
| Part 5 | DI0007 |
| Part 6 | DI0008 |
| Part 7 | DI0001 DI0002 |
| Part 8 | DI0001 DI0003 |
| Part 9 | DI0007 DI0010 |
| Part 10 | DI0010 |
What I'm looking for is for power bi to review the stirng of codes and assigned rank to the part based on the highest ranked code in the string according to the lookup table
Solution should look as follow.
| Part # | Codes | Rank |
| Part 1 | DI0001 DI0002 DI0005 | 3 |
| Part 2 | DI0001 DI0002 DI0006 | 3 |
| Part 3 | DI0001 | 1 |
| Part 4 | DI0006 | 3 |
| Part 5 | DI0007 | 3 |
| Part 6 | DI0008 | 4 |
| Part 7 | DI0001 DI0002 | 1 |
| Part 8 | DI0001 DI0003 | 2 |
| Part 9 | DI0007 DI0010 | 4 |
| Part 10 | DI0010 | 4 |
Could someone support please.
Hi Lobo1908 ,
I have created a simple sample, please refer to it to see if it helps you.
Create measures.
code1 = RIGHT(MAX('Table'[Code]),2)Measure = VAR _1 = RIGHT ( MAX ( 'Table (2)'[Codes ] ), 2 ) RETURN CALCULATE ( MAX ( 'Table'[Rank ] ), FILTER ( ALL ( 'Table' ), [code1] = _1 ) )How to Get Your Question Answered Quickly
If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .
Best Regards
Community Support Team _ Polly
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
Hi Lobo1908 ,
I have created a simple sample, please refer to it to see if it helps you.
Create measures.
code1 = RIGHT(MAX('Table'[Code]),2)Measure = VAR _1 = RIGHT ( MAX ( 'Table (2)'[Codes ] ), 2 ) RETURN CALCULATE ( MAX ( 'Table'[Rank ] ), FILTER ( ALL ( 'Table' ), [code1] = _1 ) )How to Get Your Question Answered Quickly
If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .
Best Regards
Community Support Team _ Polly
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Hi,
This basically does what I've asked for.
Unfortunately, I've simplified the example too much and is not take into considerattions some factors.
1st - my list of fault codes is much larger and some of them have a high code number but low rank e.g
Code Rank DI9001 1 DI2102 1 DI0303 2 DI0404 2 DI6005 3 DI5006 3 DI0307 3 DI0018 4 DI0019 4 DI0020 4 2 thing my code use 4 numebr insted of 2
I've manage to solve that by increasing the number form 2 to 4 in both measures.
This has created a 3rd issue, in some strings there is a value DIV090.
Every time this is present in the string the rank is blank.
I was trying to fix this by applying a step and converting DIV090 to DI0000 with rank 0.
Now everytime there is DI0000 in the string it applies rank 0 to the part even if higher rank code is present.
Following the logic in your measure it is using the fact that the codes and ranks are set in asscending order and again this is what I asked for. Unfortunetly, that doesn't solve my issue.
I've raised a new question with new data in the following thread:
Rank the string according to the highest rank valu... - Microsoft Power BI Community
I think I would split that Codes column 'To Rows' and either use the other table as a dimension table or a disconnected table and write a reasonably straightforward MAX measure.
