How to match comma separated cells values in two different columns and return missing values

[Kagliostro]

Hi Everyone,

I would like to use DAX to evaluate names differences between two columns of same table. The result will be displayed in a third column. Therefore given column A - Column B I should get the names that are listed into column A but not in column B. The peculiar part is that names in column A and Column B are Comma Separated.

A script was provided herein by a user in a previous post. However, the solution provide d ( see below) does not parse the name after the last comma.

Column = 
    VAR __PathA = SUBSTITUTE( [Column A], ",", "|" )
    VAR __PathB = SUBSTITUTE( [Column B], ",", "|" )
    VAR __CountA = LEN( [Column A] ) - LEN( SUBSTITUTE( [Column A], ",", "" ) ) + 1
    VAR __CountB = LEN( [Column B] ) - LEN( SUBSTITUTE( [Column B], ",", "" ) ) + 1
    VAR __TableA = 
        ADDCOLUMNS(
            GENERATESERIES( 1, __CountA ),
            "__Value", PATHITEM( __PathA, [Value] )
        )
    VAR __TableB = 
        ADDCOLUMNS(
            GENERATESERIES( 1, __CountB ),
            "__Value", PATHITEM( __PathB, [Value] )
        )
    VAR __Missing = EXCEPT( __TableA, __TableB )
    VAR __Result = CONCATENATEX( __Missing, [__Value], ", " )
RETURN
    __Result

this is the link to the original post 

[Kagliostro]

The issue I have now is that the drop down menu shows names aggregated rather than being listed singularly. This won't allow me to perform a proper calculation of how many times a nime is cited.

These two names should be listed separately in the table drop-down menu

[lbendlin]

Do just the EXCEPT and feed the result into the slicer.

Note: this needs to be a calculated table.

[Kagliostro]

I think I am missing something....

SME_MANCANTE = 
VAR PathA = SUBSTITUTE( IA_Assigned_to[ColumnA], ",", "|" )
VAR PathB = SUBSTITUTE( IA_Assigned_to[ColumnB], ",", "|" )
VAR TableA = SELECTCOLUMNS(GENERATESERIES( 1, PATHLENGTH(PathA)+1 ),"Value",PATHITEM( PathA, [Value] ))
VAR TableB = SELECTCOLUMNS(GENERATESERIES( 1, PATHLENGTH(PathB)+1  ),"Value",PATHITEM( PathB, [Value] ))
    VAR __Missing = EXCEPT( TableA, TableB )
    VAR __Result = CONCATENATEX( __Missing, [Value], "," )
RETURN  __Result

[lbendlin]

SME_MANCANTE = 
VAR PathA = SUBSTITUTE( IA_Assigned_to[ColumnA], ",", "|" )
VAR PathB = SUBSTITUTE( IA_Assigned_to[ColumnB], ",", "|" )
VAR TableA = SELECTCOLUMNS(GENERATESERIES( 1, PATHLENGTH(PathA)+1 ),"Value",PATHITEM( PathA, [Value] ))
VAR TableB = SELECTCOLUMNS(GENERATESERIES( 1, PATHLENGTH(PathB)+1  ),"Value",PATHITEM( PathB, [Value] ))
RETURN EXCEPT( TableA, TableB )

[Kagliostro]

It does not work, the error says "Specified a table with more than a value while a single value is expected" (apologies the error message is in Italia I did try to translate the best I could)

[lbendlin]

As I said this needs to be a CALCULATED TABLE, not a calculated column, and not a measure.

[Kagliostro]

Coud you please provide an example

[lbendlin]

[lbendlin]

__CountA and __CountB can be expressed much simpler

VAR __CountA = PATHLENGTH(__PathA)

Table = DATATABLE("A",STRING,"B",STRING,{{"One,Two,Three","Three,Four,Five"}})

X = 
VAR PathA = SUBSTITUTE( [A], ",", "|" )
VAR PathB = SUBSTITUTE( [B], ",", "|" )
VAR TableA = SELECTCOLUMNS(GENERATESERIES( 1, PATHLENGTH(PathA) ),"V",PATHITEM( PathA, [Value] ))
VAR TableB = SELECTCOLUMNS(GENERATESERIES( 1, PATHLENGTH(PathB) ),"V",PATHITEM( PathB, [Value] ))
RETURN CONCATENATEX(EXCEPT( TableA, TableB ), [V], "," )

cf: Strings list to table in DAX - SQLBI