Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

DAX to Create 2 Custom columns based on a selected value

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 ColumnExpected 2 custom Columns
Input FieldOutput Field_1Output Field_2
AAAAA;BBBBBBValues Values based on AAAAAValues 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's avatar
    Dangar332
    2 years ago

    hi, Anonymous 

     

    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's avatar
    Anonymous
    2 years ago

    Hi Anonymous 

     

    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.

3 Replies

  • Dangar332's avatar
    Dangar332
    Icon for Resident Rockstar rankResident Rockstar
    2 years ago

    hi, Anonymous 

     

    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's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Anonymous 

     

    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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

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

       

      It really helped in creating the custom fields.