Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
Anonymous
Not applicable

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

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:

 

ABC
144, 5544, 55
256, 78, 9056
3 81

 

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

 

ABCIt is in B, but not in CIt is in C, but not in B
144, 5544, 55  
256, 78, 905678, 90 
3 81 81

 

How can I do this using DAX?

 

Thanks!

1 ACCEPTED SOLUTION

 

 

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

 

 

View solution in original post

11 REPLIES 11
Anonymous
Not applicable

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.

@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],",")

@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 .

 

 

 

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(SELECTCOLUMNS(bt,"Item",[Item]),SELECTCOLUMNS(ct,"Item",[Item]))
return CONCATENATEX(bnc,[Item],",")

 

 

Anonymous
Not applicable

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

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Anonymous
Not applicable

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

 

I have two tables, tab 1 and tab2.

 

tab1:

AB
144
145
256
278
290

 

tab2:

AC
144
145
256
381

 

The output_table should be:

AB not CC not B
1  
278, 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:
 
AB not C
178, 90
278, 90
378, 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!

 

 

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

 

 

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

Anonymous
Not applicable

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

lbendlin
Super User
Super User

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)

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

lbendlin_0-1597195731590.png

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

 

Helpful resources

Announcements
July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.