Forum Discussion
Filter on Concatenatex function
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.
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]))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().
4 Replies
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]))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?
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().
