Forum Discussion

OKgo's avatar
OKgo
Icon for Helper IV rankHelper IV
8 years ago
Solved

DAX help with LEN & SUBSTITUTE

I have a formula that works great in Excel, IF([@[Assigned]]="","",(LEN([@[Assigned]])-LEN(SUBSTITUTE([@[Assigned]],"ASSIGNED","")))/8"))   Example entry from Table[Assigned]: ASSIGNED_Material I...
Need Help
  • ChrisMendoza's avatar
    ChrisMendoza
    8 years ago

    OKgo,

     

    Try:

    Column =
IF (
    'Table'[Assigned] = BLANK (),
    BLANK (),
    DIVIDE (
        LEN ( 'Table'[Assigned] )
            - LEN ( SUBSTITUTE ( 'Table'[Assigned], "ASSIGNED", BLANK () ) ),
        8
    )
)
ChrisMendoza's avatar
ChrisMendoza
Icon for Resident Rockstar rankResident Rockstar
8 years ago

OKgo,

 

I am having trouble deciphering your column headers and their values. Please format as table or provide datafile sample.

  • ChrisMendoza's avatar
    ChrisMendoza
    Icon for Resident Rockstar rankResident Rockstar
    8 years ago

    OKgo,

     

    Try:

    Column =
IF (
    'Table'[Assigned] = BLANK (),
    BLANK (),
    DIVIDE (
        LEN ( 'Table'[Assigned] )
            - LEN ( SUBSTITUTE ( 'Table'[Assigned], "ASSIGNED", BLANK () ) ),
        8
    )
)
    • OKgo's avatar
      OKgo
      Icon for Helper IV rankHelper IV
      8 years ago

      Its beautiful! Thank you ChrisMendoza