topic Re: Search a text in each row then create a new column in DAX Commands and Tips

Search a text in each row then create a new column

MB_PowerBI — Fri, 23 Dec 2022 01:29:52 GMT

Hello,

I have the following sample data

ID	DATA
1	A
1	A
2	A
2	B
3	B

My need is to create a third column, which will check for each ID,
if there is at least and only A then list A
if there is at least and only B then list B
Else (there is at least one A and one B then A & B

ID	DATA	Result
1	A	A
1	A	A
2	A	A&B
2	B	A&B
3	B	B

Thanks for your help.

Regards,

Re: Search a text in each row then create a new column

FreemanZ — Fri, 23 Dec 2022 02:00:32 GMT

hi MB_PowerBI

try to add a column like this:

Result2 = VAR _id = [ID] VAR _table= CALCULATETABLE( VALUES(TableName[Data]), ALL(TableName), TableName[ID] = _id ) RETURN CONCATENATEX( _table, TableName[Data], "&" )

it works like this:

Re: Search a text in each row then create a new column

FreemanZ — Fri, 23 Dec 2022 02:02:38 GMT

or something like this:

Result2 = VAR _table= CALCULATETABLE( VALUES(TableName[Data]), ALLEXCEPT(TableName, TableName[ID]) ) RETURN CONCATENATEX( _table, TableName[Data], "&" )

Re: Search a text in each row then create a new column

CNENFRNL — Fri, 23 Dec 2022 19:56:14 GMT

For fun only, a showcase of powerful Excel worksheet formula,