The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
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!
Solved! Go to 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
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],",")
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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:
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!
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?
My intention is to do it via calculated tables and columns.
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],",")
For the other measure you just need to swap the order of the tables in the EXCEPT function.
User | Count |
---|---|
25 | |
10 | |
8 | |
7 | |
6 |
User | Count |
---|---|
32 | |
12 | |
10 | |
10 | |
9 |