'Searching a row for a substring in another table with exact match
I'm trying to use search in a description to see if there is an exact match to the field in another table. I used the code from How to search a row for a substring from another table?, but this did not give an exact match.
For example I have two tables: Contract_Ref and Contract Description and the code I got from the post above generates the Actual Result because it finds the 1 first, but I want it to find an exact match.
I was wondering if I could create a while loop for the search to loop until it finds an exact match but it doesn't appear that DAX in Power Pivot supports that.
T_Contract_Ref
|Contract Number |
|:---------------|
|1 |
|100 |
|1100 |
|1110 |
T_Contracts_Description
|Description | Desired Result| Actual Result|
|:--------------------|:--------------|:-------------|
|contract 1110 | 1110 | 1 |
|supplier contract 100| 100 | 1 |
|contract supplier 1 | 1 | 1 |
=VAR CurrentRowDescription = T_Contracts_Description[Description]
VAR MatchingRowsFromTable1 =
FILTER(T_Contract_Ref,
SEARCH(T_Contract_Ref[Contract Number], CurrentRowDescription,1, 0) >0
)
VAR OnlyMatchingRow = SAMPLE(1,MatchingRowsFromTable1,T_Contract_Ref[Contract_Number],ASC)
Return
SELECTCOLUMNS(OnlyMatchingRow, "Budget Ref",T_Contract_Ref[Upper_Number])
Thanks in advance for any help. This is my first post so apologies if any of the formatting isn't correct.
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
