Calculated Column Help

[sshanssun]

Hi, I have a 'calculated column A' from a table with multiple or same values separated by comma. I need another 'calculated column B' to show whether that 'calculated column A' is single, multiple or others, example is provided below, appreciate your help:

Table1:

Calculated Column A	Calculated Column B
,Elix A	Single
,Elix A,Elix B,	Multiple
,Elix B,	Single
,Elix A,Elix C,Elix D,	Multiple
,Elix A,ElixA,	Single
..	Others
,	Others
,,,,,	Others

showed in a picture as below, 

Thanks in advance for the support

[lbendlin]

Same as before. You need to use PATH functions.

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" )

"Elix A"  and "ElixA"  are not the same string.

[sshanssun]

Hi, Thank you so much. I ran into one more issue. My outcome under 'Calculated Column A' with a multiple comma delimiter has different data value which means those could not be found in the original table and hence those were left blanks with commas. for example,

If the Calculated Column A has a value of ",Elix A,,," and there is a comma before Elix A and after which means, there are value with a delimiter which is blank and this should be Multiple. I apologize that I didn't specifiy this very cleary when I sent it originally and it is my mistake.

Example and expected output table,

Calculated Column A	Calculated Column B
,Elix A	Multiple
Elix A,Elix A	Single
Elix B	Single
Elix B,,,	Multiple
,,	Others
Elix C	Single
Elix D,	Multiple
,Elix D,Elix D	Multiple
,	Others
Elix A,Elix B,	Multiple
Elix D,Elix D	Single

table in picture format, 

 Thank you once again for the help.

[Anonymous · Accepted solution]

Hi @sshanssun,

Thank you for reaching out in Microsoft Community Forum.

Thank you @lbendlin  for the helpful response.

Please use below DAX for required output;

Calculated Column B =
VAR a = SUBSTITUTE([Calculated Column A], ",", "|")
VAR PathLength = PATHLENGTH(a)
VAR AllItems =
ADDCOLUMNS (
GENERATESERIES(1, PathLength),
"v", PATHITEM(a, [Value])
)
VAR CleanItems =
FILTER ( AllItems, NOT ISBLANK([v]) && [v] <> "" )
VAR DistinctItems =
SUMMARIZE ( CleanItems, [v] )
VAR HasBlanks =
COUNTROWS(AllItems) > COUNTROWS(CleanItems)
VAR DistinctCount = COUNTROWS(DistinctItems)
RETURN
SWITCH (
TRUE(),
DistinctCount = 0, "Others",
DistinctCount = 1 && NOT HasBlanks, "Single",
TRUE, "Multiple"
)

Please continue using Microsoft community forum.

If you found this post helpful, please consider marking it as "Accept as Solution" and give it a 'Kudos'. if it was helpful. help other members find it more easily.

Regards,
Pavan.

[sshanssun]

Thank you so much, Pavan. It worked!