DAX to Create 2 Custom columns based on a selected value

[Anupal]

Hello Members,

I am looking help with a DAX to create2 custom columns based on inputs from original field.

there are mulitple values in the field which- 1)- have to grouped based on a filter calue and then 2)- 2 custom fields to be created based on that grouping and filter value-

Eg: Below-

Current Input Column	Expected 2 custom Columns
Input Field	Output Field_1	Output Field_2
AAAAA;BBBBBB	Values Values based on AAAAA	Values Values based on BBBBBB
AAAAAA;CCCCCC
AAAAA;DDDDDD
BBBBBB;EEEEEEE
FFFFFFFF;HHHHHHH
HHHHHHH;GGGGGG

Here my Input(actual field has certain text values.

Now i want to create 2 fields based on criteria where Values contain AAAAA and BBBBB

So 2 fields needs tobe created, one will provide only values containing AAAAA and other field will provide values containing BBBBB

How can this be achived.

Thanks

AP

[Dangar332 · Accepted solution]

hi, @Anupal 

Add two columns in your current table
try below column code

outpu_field1=
VAR a = SEARCH(";",'Table (2)'[Current Input Column])
return
LEFT('Table (2)'[Current Input Column],a-1)

output_field2=
VAR a = SEARCH(";",'Table (2)'[Current Input Column])
var b = LEN('Table (2)'[Current Input Column])
return
RIGHT('Table (2)'[Current Input Column],b-a)

and it easy in power query

use split column

[Anonymous · Accepted solution]

Hi @Anupal 

@Dangar332 , thanks for your contribution to the forum

Just as he posted that you can add 2 calculate column.

And I'll add some details on his basis:

First of all, I create a new table:

Table2 =
SELECTCOLUMNS (
    'Table',
    "outpu_field", LEFT ( 'Table'[Current Input], SEARCH ( ";", 'Table'[Current Input] ) - 1 )
)

Then add a new measure:

Output =
VAR _newtable =
    SELECTEDVALUE ( Table2[outpu_field] )
VAR _newoutput =
    SWITCH (
        _newtable,
        "AAAAA", "AAAAA",
        "BBBBBB", "BBBBBB",
        "FFFFFFFF", "FFFFFFFF"
    )
RETURN
    CONCATENATEX (
        ALL ( 'Table' ),
        CALCULATE (
            SELECTEDVALUE ( 'Table'[output_field2] ),
            'Table'[output_field1] = _newoutput
        ),
        " "
    )

The result is as follow:

Best Regards

Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anupal]

Hello @Anonymous  and @Dangar332  many thanks for the responses and the solution provided.

It really helped in creating the custom fields.

[Dangar332 · Accepted solution]

hi, @Anupal 

Add two columns in your current table
try below column code

outpu_field1=
VAR a = SEARCH(";",'Table (2)'[Current Input Column])
return
LEFT('Table (2)'[Current Input Column],a-1)

output_field2=
VAR a = SEARCH(";",'Table (2)'[Current Input Column])
var b = LEN('Table (2)'[Current Input Column])
return
RIGHT('Table (2)'[Current Input Column],b-a)

and it easy in power query

use split column