Forum Discussion
Helper IVDAX 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 ID 123- 600010 - Lot: 4444 (31-Aug-2018), ASSIGNED_Material ID 333- 6023 - Lot: 3133 (31-Aug-2019)
LEN count ASSIGNED = 16 characters divided by the amount of letters in ASSIGNED = answer = 2
I am struggling with the DAX equivilant
=
IF (
Table[Assigned] = BLANK (),
BLANK (),
(
LEN ( Table[Assigned] )
- LEN (
SUBSTITUTE ( Table[Assigned], "ASSIGNED", BLANK () ) / 8)
)
)
)Tips greatly apprecaited
OKgo,
Try:
Column = IF ( 'Table'[Assigned] = BLANK (), BLANK (), DIVIDE ( LEN ( 'Table'[Assigned] ) - LEN ( SUBSTITUTE ( 'Table'[Assigned], "ASSIGNED", BLANK () ) ), 8 ) )
5 Replies
- ChrisMendoza
Resident Rockstar
OKgo,
I am having trouble deciphering your column headers and their values. Please format as table or provide datafile sample.
OKgoChrisMendoza, Thank you for checking in. Hope this helps
https://www.dropbox.com/s/jtasi1b8cu6mdi6/PBI%20Forum.xlsx?dl=0
- ChrisMendoza
OKgo,
Try:
Column = IF ( 'Table'[Assigned] = BLANK (), BLANK (), DIVIDE ( LEN ( 'Table'[Assigned] ) - LEN ( SUBSTITUTE ( 'Table'[Assigned], "ASSIGNED", BLANK () ) ), 8 ) )
- OKgo
In early construction of the Dax formula this error popped up from time to time but then dissappeared.
Calculation error in column 'Table[Assigned]" xxxx of type text to type number