Filter on Concatenatex function

[ArielK]

Dear Experts

I am beginner DAX user and I am trying to create filter that combine data from two columns containing names.

source data looks something like this:

Name1	Name2
A	B
A	C
C	A
B	A
C	A

I would like the filter to treat AB the same as BA. My idea was to create additional column using "Concatenatex" and sort result alphabetically. The problem is that function join data from all rows and I run out of ideas how to restrict context just for only one, current row.

For now, it looks like this:

Name1	Name2	New column
A	B	AB AC AC AB AC
A	C	AB AC AC AB AC
C	A	AB AC AC AB AC
B	A	AB AC AC AB AC
C	A	AB AC AC AB AC

Structure I would like to achieve is:

Name1	Name2	New column
A	B	AB
A	C	AC
C	A	AC
B	A	AB
C	A	AC

Or maybe there is other solution to this case? I would appreciate all your help.

[johnyip · Accepted solution]

I assume you use this DAX for the calculated column.

CONCATENATEX('Table',[Name1] & [Name2], " ")

But instead, you should use the following.

Column = IF([Name1]<[Name2], [Name1] & [Name2], [Name2] & [Name1])

If you wish to use a measure instead, use the following:

Measure = IF(MAX([Name1])<MAX([Name2]), MAX([Name1]) & MAX([Name2]), MAX([Name2]) & MAX([Name1]))

[johnyip · Accepted solution]

I assume you use this DAX for the calculated column.

CONCATENATEX('Table',[Name1] & [Name2], " ")

But instead, you should use the following.

Column = IF([Name1]<[Name2], [Name1] & [Name2], [Name2] & [Name1])

If you wish to use a measure instead, use the following:

Measure = IF(MAX([Name1])<MAX([Name2]), MAX([Name1]) & MAX([Name2]), MAX([Name2]) & MAX([Name1]))

[ArielK]

Thank you!

I used your “column” solution, and It seems so obvious now.. but as a newbie I try to overcomplicate everything using build in functions.

On the other hand, I wouldn’t think of using “<” on text. 😉

And as regards our measure, what is the “MAX” used for? It seems like it would always return the same value. Or am I misunderstanding that?

[johnyip · Accepted solution]

@ArielK 

For measure, there is row context.

Measures are evaluated row by row, depending on the visuals.

If you are using a table, the current "row" will be showing one value,

so MAX() of that value of the "row" will always be that exact one value.

And please accept my asnwer a the solution so others with the same problem can look for this thread.

You can also use MIN(), AVERAGE(), etc. But by the time I was learning and/or asking, I found most people use MAX().

[ArielK]

Both already accepted. 🙂

Thank you very much for your time and support.