Forum Discussion
CONCATENATEX gives different result
You use SEARCH which has no idea about the structure of your strings (the delimiters). You need to use PATH functions.
Result = VAR p = SUBSTITUTE ( [Value], ",", "|" ) RETURN CONCATENATEX ( ADDCOLUMNS ( GENERATESERIES ( 1, PATHLENGTH ( p ) ), "s", VAR v = [Value] RETURN MAXX ( FILTER ( 'Table 2', PATHITEM ( p, v ) = [Value] ), [Category] ) ), [s], "," )
You use SEARCH which has no idea about the structure of your strings (the delimiters). You need to use PATH functions.
Result =
VAR p =
SUBSTITUTE ( [Value], ",", "|" )
RETURN
CONCATENATEX (
ADDCOLUMNS (
GENERATESERIES ( 1, PATHLENGTH ( p ) ),
"s",
VAR v = [Value]
RETURN
MAXX ( FILTER ( 'Table 2', PATHITEM ( p, v ) = [Value] ), [Category] )
),
[s],
","
)
It worked fine, thank you so much!
HI lbendlin - I missed to add one more point, if the value from Table 1 is not available in the lookup on Table 2, the Category result column can have "Others", this may help identify to filter the non-available lookup values. I know I already accepted as solution, wondering this request can also be accomodated.
Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.Thanks again. I was able to use the following DAX and it was successful, thank you!
Calculated Column B =
VAR a =
SUBSTITUTE ( [Calculated Column A], ",", "|" )
VAR b =
COALESCE (
COUNTROWS (
SUMMARIZE (
FILTER (
ADDCOLUMNS (
GENERATESERIES ( 1, PATHLENGTH ( a ) ),
"v", PATHITEM ( a, [Value] )
),
[v] > ""
),
[v]
)
),
0
)
RETURN
SWITCH ( b, 0, "Others", 1, "Single", "Multiple" )
