topic Re: How to match comma separated cells values in two different columns and return missing values in DAX Commands and Tips

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

Kagliostro — Thu, 12 Sep 2024 15:59:11 GMT

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

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

lbendlin — Thu, 12 Sep 2024 18:10:22 GMT

__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

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

Kagliostro — Thu, 26 Sep 2024 11:09:38 GMT

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

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

lbendlin — Thu, 26 Sep 2024 11:35:09 GMT

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

Note: this needs to be a calculated table.

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

Kagliostro — Thu, 26 Sep 2024 12:08:45 GMT

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

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

lbendlin — Thu, 26 Sep 2024 12:19:44 GMT

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

Kagliostro — Thu, 26 Sep 2024 12:27:23 GMT

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)

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

lbendlin — Thu, 26 Sep 2024 12:35:00 GMT

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

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

Kagliostro — Thu, 26 Sep 2024 13:01:24 GMT

Coud you please provide an example

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

lbendlin — Thu, 26 Sep 2024 13:23:30 GMT