cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
vijenkin
Microsoft
Microsoft

IF Table Column Contains Multiple Unique Values use the Highest Index

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 TitleIndex
There is a problem with 0123451
67890 spike over the last four weeks 2
Many devices hitting 24680 as of last week3
67890 spike over the last two weeks 4

 

Table B

Unique CodeSymbolic Name
0Success
1Security Error
2Server Error
..........
000000Client Error
000001Network Error
.........
012345Network Error
67890Server Error
24680Client 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 TitleNew Title
There is a problem with 012345012345 (Network Error)
67890 spike over the last two weeks 67890 (Server Error)
Many devices hitting 24680 as of last week24680 (Client Error)
Help needed for system problemHelp 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.

 

 

3 REPLIES 3
Ashish_Mathur
Super User III
Super User III

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)

Untitled.png

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Greg_Deckler
Super User IV
Super User IV

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


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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. 

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.