Forum Discussion

awitt's avatar
awitt
Icon for Helper III rankHelper III
4 years ago

Table 2 Contains string of table 1

I'm trying to return a result (red column) based on if a value in table 2 appears in part of a value in table 1. Table 1 (left) contains orders data with a tracking number which can include additiona...
Need Help
smpa01's avatar
smpa01
Icon for Community Champion rankCommunity Champion
4 years ago

awitt  you can use a measure like this

Measure =
VAR _len1 =
    FIND ( " -", MAX ( 'Order Table'[Tracking Number] ), 1, 0 )
VAR _len2 =
    IF ( _len1 = 0, 0, _len1 - 1 )
VAR _string =
    CONVERT (
        IF (
            _len1 = 0,
            MAX ( 'Order Table'[Tracking Number] ),
            LEFT ( MAX ( 'Order Table'[Tracking Number] ), _len2 )
        ),
        INTEGER
    )
VAR _x =
    CALCULATE (
        MAX ( TrackingDataDetail[Charge Desc] ),
        TREATAS ( { _string }, TrackingDataDetail[Tracking Number] ),
        REMOVEFILTERS ( 'Order Table' )
    )
RETURN
    IF ( _x <> BLANK (), 1, 0 )