topic Re: Compare two cells containing a list separated by comma and return distinct values in DAX Commands and Tips

Compare two cells containing a list separated by comma and return distinct values

Anonymous — Tue, 11 Aug 2020 14:15:27 GMT

Hi guys!

A have a table with 3 columns: A, B and C. The columns B and C contains a list separated by comma (I created it using the concatenatex function).

I want to compare the columns B and C, and return the values that are in column B, but not in C, and vice versa.

Therefore, for example, I have this table now:

A	B	C
1	44, 55	44, 55
2	56, 78, 90	56
3		81

And I want to add two new columns so the output is:

A	B	C	It is in B, but not in C	It is in C, but not in B
1	44, 55	44, 55
2	56, 78, 90	56	78, 90
3		81		81

How can I do this using DAX?

Thanks!

Re: Compare two cells containing a list separated by comma and return distinct values

lbendlin — Wed, 12 Aug 2020 01:09:12 GMT

Are the spaces after the comma part of the values or should they be trimmed away?

Is your table a calculated table or a table variable? (ie can it be done via calculated columns or does it need to be done via measures?)

Does it have to be DAX or is it ok if this is done in Power Query? (Hint: that would be soo much easier)

Re: Compare two cells containing a list separated by comma and return distinct values

lbendlin — Wed, 12 Aug 2020 01:29:35 GMT

Here is an extremely crude implementation. I am not proud of it but it does do the job in very specific circumstances (no more than eight values in each cell)

B not C = var b=SUBSTITUTE(SELECTEDVALUE(CompareColumns[B]),",","|") var c=SUBSTITUTE(SELECTEDVALUE(CompareColumns[C]),",","|") var bt = {PATHITEM(b,1),PATHITEM(b,2),PATHITEM(b,3),PATHITEM(b,4),PATHITEM(b,5),PATHITEM(b,6),PATHITEM(b,7),PATHITEM(b,8)} var ct = {PATHITEM(c,1),PATHITEM(c,2),PATHITEM(c,3),PATHITEM(c,4),PATHITEM(c,5),PATHITEM(c,6),PATHITEM(c,7),PATHITEM(c,8)} var bnc = EXCEPT(bt,ct) return CONCATENATEX(bnc,[Value],",")

For the other measure you just need to swap the order of the tables in the EXCEPT function.

Re: Compare two cells containing a list separated by comma and return distinct values

mahoneypat — Wed, 12 Aug 2020 01:42:35 GMT

This would be easier to do in the query editor, but it is doable in DAX too. However, it is hard to do in the concatenated form (as lbendlin demonstrated). If you can generate a virtual table of values to use in CONCATENATEX, you could use the same virtual tables to use with the EXCEPT function to return a table with the values in one but not the other (and then use CONCATENATEX to display that smaller table). If needed, you can provide sample data in the unconcatenated form for a proposed DAX solution.

If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.

Regards,

Pat

Re: Compare two cells containing a list separated by comma and return distinct values

Anonymous — Wed, 12 Aug 2020 11:49:58 GMT

Look at this:

EVALUATE var __string = "44,22,21,24" var __stringWithPipe = SUBSTITUTE(__string, ",", "|") var __itemCount = PATHLENGTH(__stringWithPipe) var __choppedString = generate( GENERATESERIES(1, __itemCount, 1), row( "Item", PATHITEM( __stringWithPipe, [Value] ) ) ) return __choppedString

Just run the above in DAX Studio or any other DAX editor. It shows you how to chop a delimited string into a virtual table. From this you can create the solution yourself using EXCEPT.

Re: Compare two cells containing a list separated by comma and return distinct values

lbendlin — Wed, 12 Aug 2020 13:09:11 GMT

Anonymous Great stuff. But there is a little snag when the cells are empty.

Calculation error in measure 'CompareColumns'[B not C]: The arguments in GenerateSeries function cannot be blank.

Any idea how to work around that?

Edit: never mind...

B not C = var b="" & SUBSTITUTE(SELECTEDVALUE(CompareColumns[B]),",","|") var c="" & SUBSTITUTE(SELECTEDVALUE(CompareColumns[C]),",","|") var bt = GENERATE(GENERATESERIES(1,PATHLENGTH(b),1),ROW("Item",PATHITEM(b,[Value]))) var ct = GENERATE(GENERATESERIES(1,PATHLENGTH(c),1),ROW("Item",PATHITEM(c,[Value]))) var bnc = EXCEPT(bt,ct) return CONCATENATEX(bnc,[Item],",")

Re: Compare two cells containing a list separated by comma and return distinct values

Anonymous — Wed, 12 Aug 2020 17:57:15 GMT

I liked your ideas. So, let me redo the question.

I have two tables, tab 1 and tab2.

tab1:

A	B
1	44
1	45
2	56
2	78
2	90

tab2:

A	C
1	44
1	45
2	56
3	81

The output_table should be:

A	B not C	C not B
1
2	78, 90
3		81

I have tried to develop the output table using the DAX below:

NEW TABLE:

output_table = DISTINCT(UNION(DISTINCT(tab1[A]),DISTINCT(tab2[A])))

NEW COLUMN:

B not C =

CONCATENATEX(

CALCULATETABLE(

EXCEPT(

CALCULATETABLE(SUMMARIZE(tab1,tab1[B]),FILTER(tab1,tab1[A]=2)),

CALCULATETABLE(SUMMARIZE(tab2,tab2[C]),FILTER(tab2,tab2[A]=2))

)

CALCULATETABLE(

EXCEPT(

CALCULATETABLE(SUMMARIZE(tab1,tab1[B]),FILTER(tab1,tab1[A]=2)),

CALCULATETABLE(SUMMARIZE(tab2,tab2[C]),FILTER(tab2,tab2[A]=2))

)

", ")

Then, so far I have this output:

A	B not C
1	78, 90
2	78, 90
3	78, 90

I am struggling to change the filter to work like "FILTER(tab1,tab1[A]=output_table[A])", but it doesn`t work.

Can you help me?
Thanks!

Re: Compare two cells containing a list separated by comma and return distinct values

lbendlin — Wed, 12 Aug 2020 18:18:16 GMT

That's an entirely different scenario. Are you now saying that this can be done via calculated columns? Or even in Power Query?

Re: Compare two cells containing a list separated by comma and return distinct values

Anonymous — Wed, 12 Aug 2020 18:39:50 GMT

My intention is to do it via calculated tables and columns.

Re: Compare two cells containing a list separated by comma and return distinct values

lbendlin — Wed, 12 Aug 2020 19:01:40 GMT

Anonymous One more issue. The introduction of the Value column throws off the EXCEPT function because EXCEPT compares entire rows, not individual columns. We need to get rid of the Value column first .

Re: Compare two cells containing a list separated by comma and return distinct values

Anonymous — Wed, 12 Aug 2020 19:12:50 GMT

Guys, my query was supposed to give you an idea about how to approach this problem, not a total solution to it.

Re: Compare two cells containing a list separated by comma and return distinct values

lbendlin — Wed, 12 Aug 2020 22:58:26 GMT

NEW TABLE = var t = DISTINCT(SELECTCOLUMNS(UNION(Tab1,Tab2),"An",[A])) var n = ADDCOLUMNS(t, "B not C", CONCATENATEX( EXCEPT( SELECTCOLUMNS(FILTER(Tab1,Tab1[A]=[An]),"B",Tab1[B]), SELECTCOLUMNS(FILTER(Tab2,Tab2[A]=[An]),"-",Tab2[C]) ), [B],","), "C not B", CONCATENATEX( EXCEPT( SELECTCOLUMNS(FILTER(Tab2,Tab2[A]=[An]),"B",Tab2[C]), SELECTCOLUMNS(FILTER(Tab1,Tab1[A]=[An]),"-",Tab1[B]) ), [B],",") ) return n