Find a code in a string with the highest rank

[Lobo1908]

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.

[Anonymous · Accepted solution]

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.

[Lobo1908]

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

[HotChilli]

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.