Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Lookup name from concatenated text

Hi Community !

 

I am trying for a DAX function which will let me 'Find'  a list of Name (reference table) in a column (in current table) with ";-delimted" names. For ex: 

 

Current Table Column has:

Row 1: Name 1; Name 2: Name 8

Row 2: Name 4; Name 5; Name 6

Row 3: Name 2; Name 7

Row 4: Name 1; Name 4

 

Reference Table has:

Name 1

Name 3

Name 5

 

Then result 'Add Column' should be:

Row 1: Name 1

Row 2: Name 5

Row 3: null

Row 4: Name 1

 

Thanks in advance !

Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    4 years ago

    Hi,

    I am not sure if the below solution works for your datamodel, but please check the below picture and the attached pbix file.

     

     

     

    New Column CC =
VAR _list =
    DISTINCT ( 'Name'[Name] )
VAR _newtable =
    ADDCOLUMNS ( Data, "@Path", SUBSTITUTE ( Data[Name], "; ", "|" ) )
VAR _condition =
    ADDCOLUMNS (
        _newtable,
        "Path1", IF ( PATHITEM ( [@Path], 1 ) IN _list, PATHITEM ( [@Path], 1 ) ),
        "Path2", IF ( PATHITEM ( [@Path], 2 ) IN _list, PATHITEM ( [@Path], 2 ) ),
        "Path3", IF ( PATHITEM ( [@Path], 3 ) IN _list, PATHITEM ( [@Path], 3 ) )
    )
VAR _summarizetable =
    SUMMARIZE (
        ADDCOLUMNS (
            _condition,
            "@Expectedresult",
                IF (
                    MAX ( MAX ( [Path1], [Path2] ), [Path3] ) = BLANK (),
                    "null",
                    MAX ( MAX ( [Path1], [Path2] ), [Path3] )
                )
        ),
        Data[Category],
        [@Expectedresult]
    )
RETURN
    MAXX (
        FILTER ( _summarizetable, Data[Category] = EARLIER ( Data[Category] ) ),
        [@Expectedresult]
    )

     

2 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    4 years ago

    Hi,

    I am not sure if the below solution works for your datamodel, but please check the below picture and the attached pbix file.

     

     

     

    New Column CC =
VAR _list =
    DISTINCT ( 'Name'[Name] )
VAR _newtable =
    ADDCOLUMNS ( Data, "@Path", SUBSTITUTE ( Data[Name], "; ", "|" ) )
VAR _condition =
    ADDCOLUMNS (
        _newtable,
        "Path1", IF ( PATHITEM ( [@Path], 1 ) IN _list, PATHITEM ( [@Path], 1 ) ),
        "Path2", IF ( PATHITEM ( [@Path], 2 ) IN _list, PATHITEM ( [@Path], 2 ) ),
        "Path3", IF ( PATHITEM ( [@Path], 3 ) IN _list, PATHITEM ( [@Path], 3 ) )
    )
VAR _summarizetable =
    SUMMARIZE (
        ADDCOLUMNS (
            _condition,
            "@Expectedresult",
                IF (
                    MAX ( MAX ( [Path1], [Path2] ), [Path3] ) = BLANK (),
                    "null",
                    MAX ( MAX ( [Path1], [Path2] ), [Path3] )
                )
        ),
        Data[Category],
        [@Expectedresult]
    )
RETURN
    MAXX (
        FILTER ( _summarizetable, Data[Category] = EARLIER ( Data[Category] ) ),
        [@Expectedresult]
    )

     

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Anonymous Maybe:

    Column =
  VAR __Ref = DISTINCT('RefTable'[Column])
  VAR __Num = LEN('Table'[Column]) - LEN(SUBSTITUTE([Column],";","")) + 1
  VAR __Text = SUBSTITUTE([Column],"; ", "|")
  VAR __Table = 
    SELECTCOLUMNS(
      ADDCOLUMNS(
        GENERATESERIES(1, __Num, 1),
        "__Column",PATHITEM(__Text,[Value],TEXT)
      ),
      "Column",[__Column]
    )
RETURN
  MAXX(INTERSECT(__Table, __Ref), [Column])