Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Lobo1908
Helper I
Helper I

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:

CodeRank 
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 2DI0001 DI0002 DI0006
Part 3DI0001
Part 4DI0006
Part 5DI0007
Part 6DI0008
Part 7DI0001 DI0002
Part 8DI0001 DI0003
Part 9DI0007 DI0010
Part 10DI0010

 

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 2DI0001 DI0002 DI0006   3
Part 3DI0001   1
Part 4DI0006   3
Part 5DI0007   3
Part 6DI0008   4
Part 7DI0001 DI0002   1
Part 8DI0001 DI0003   2
Part 9DI0007 DI0010   4
Part 10DI0010   4

 

Could someone support please.

1 ACCEPTED SOLUTION
v-rongtiep-msft
Community Support
Community Support

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

vpollymsft_0-1670210217658.png

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.

 

View solution in original post

3 REPLIES 3
v-rongtiep-msft
Community Support
Community Support

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

vpollymsft_0-1670210217658.png

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 

 

CodeRank 
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
Super User
Super User

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.

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.