Forum Discussion
Solved
Sort concatenated look up values
Hi, I have concatenated two lookup fields from different tables into a master table, but am getting different combinations of the same values. I cant merge or append these tables unfortunately so t...
Hi Bren_L1 ,
Here are the steps you can follow：
1. Create calculated table.
Table 2 = var _column1= SELECTCOLUMNS('Table',"Column1",[Column1]) var _column2= SELECTCOLUMNS('Table',"Column2",[Column2]) var _table= DISTINCT( UNION( _column1,_column2)) return ADDCOLUMNS( _table,"rank",RANKX(_table,[Column1],,ASC))2. Create calculated column.
Rank_index = var _rank1= MAXX( FILTER( 'Table 2','Table 2'[Column1]=EARLIER('Table'[Column1])),[rank]) var _rank2= MAXX( FILTER( 'Table 2','Table 2'[Column1]=EARLIER('Table'[Column2])),[rank]) return IF( _rank1<=_rank2, [Column1]&","&[Column2], [Column2]&","&[Column1])3. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Hi Bren_L1 ,
Here are the steps you can follow：
1. Create calculated table.
Table 2 =
var _column1=
SELECTCOLUMNS('Table',"Column1",[Column1])
var _column2=
SELECTCOLUMNS('Table',"Column2",[Column2])
var _table=
DISTINCT(
UNION(
_column1,_column2))
return
ADDCOLUMNS(
_table,"rank",RANKX(_table,[Column1],,ASC))
2. Create calculated column.
Rank_index =
var _rank1=
MAXX(
FILTER(
'Table 2','Table 2'[Column1]=EARLIER('Table'[Column1])),[rank])
var _rank2=
MAXX(
FILTER(
'Table 2','Table 2'[Column1]=EARLIER('Table'[Column2])),[rank])
return
IF(
_rank1<=_rank2,
[Column1]&","&[Column2],
[Column2]&","&[Column1])3. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Thanks this worked as required, really appreciate the assistance 🙂