IF Table Column Contains Multiple Unique Values use the Highest Index

[vijenkin]

Based on a solution that came about from a previous thread Solved: Re: IF Table Column Contains Matching Value Place ... - Microsoft Power BI Community, I am trying to solve an unexpected problem after using the report in Production. 

Context Summary

I have two tables that have no relationship to each other. Table A contains a column with text that is user generated. Table B contains two columns that have a Unique Code and its corresponding Symbolic Name.

Table A

Original Title	Index
There is a problem with 012345	1
67890 spike over the last four weeks	2
Many devices hitting 24680 as of last week	3
67890 spike over the last two weeks	4

Table B

Unique Code	Symbolic Name
0	Success
1	Security Error
2	Server Error
....	......
000000	Client Error
000001	Network Error
....	.....
012345	Network Error
67890	Server Error
24680	Client Error

Initital Result

This resulted in adding a column that pulls the Unique Code from the Original Title {Table A] and the Symbolic Name [Table B]

Original Title	New Title
There is a problem with 012345	012345 (Network Error)
67890 spike over the last two weeks	67890 (Server Error)
Many devices hitting 24680 as of last week	24680 (Client Error)
Help needed for system problem	Help needed for system problem

Problem

After some modification I ended up getting the result I needed, however, as with any engineering, problems arise outside of testing. One of the teams who generates Table A will often create an exact copy of one of the rows. As I was originally utilizing the Unique Code as a Key with a Many to One relationship PowerBi sees multiple rows when searching for Unique Code and has no idea what to do, causing the DAX to fail. Fortunately by the nature of our system each of the Rows in Table A has an Index number that will always be unique. This got us thinking when searching through Table A we can utilize the latest / Max Index value from Table A to only pull the row with the highest Index. Given the dax code provided below, what would be the best means to go about this without losing the current functionality?  

Current DAX Code

Unique Status Code = 
IF (
    ISBLANK (
        LASTNONBLANK (
            FILTER (
                VALUES ( Table B[UniqueCode] ),
                SEARCH ( Table B[UniqueCode], Table A[Title], 1, 0 )
            ),
            1
        )
    ),
    Title,
    LASTNONBLANK (
        FILTER (
            VALUES ( Table B[UniqueCode] ),
            SEARCH ( Table B[Uniqueode], Table A[Title], 1, 0 )
        ),
        1
    )
)

I will not be able to share the PBIX file or provide screenshots of the actual data for privacy reasons

Any help or direction would be greatly appreciated. If this is not possible or more information is needed, please feel free to let me know. Thanks.

[vijenkin]

Any help or direction would be greatly appreciated. If this is not possible or more information is needed, please feel free to let me know. Thanks.

[Ashish_Mathur]

Hi,

In the first place, I do not see how you can build a relationship between Table A and Table B.  Also, to solve this question, you do not need to build a relationship between the 2 tables at all.  Write this calculated column formula in Table A

=LASTNONBLANK(FILTER(VALUES(TableB[Unique Code]),SEARCH(TableB[Unique Code],TableA[Original Title],1,0)),1)

I know the answer in the first row is wrong, but more importantly repetitions in Table A are not causing an DAX failure.

[Greg_Deckler]

@vijenkin Are you looking for something like LOOKUP MIN/MAX? Lookup Min/Max - Microsoft Power BI Community

[vijenkin]

HI @Greg_Deckler That was originally the plan I was going with; to use MAXX, however, it doesn't quite work. Is there perhaps a way to modify the DAX code that I have above to essentially in this order:

1. Pull the Unique Code from the Title
2. Search for the highest Index value if there are multople
3. Return the Row for the highest index

I suspect no as I would need to pull the Unique Code and use that as a key for the MAXX but I'd thought I'd check. 

II've been trying to solve the porblem for the past few hours and it seems no matter how I modify the solution the main crux of the problem is the relationship between Table A and Table B which does need a relationship. Otherwise I wouldn't be able to connect get the Symbolic Name associated with the Unique Code or correlate the Table.A[Unique Code] with the corresponding metric data from Table C, which I know I didn't mention in order to keep things simple. Problem being the Unique Codes in Table B are text values and PowerBi can't build a Many to One relationship.