Forum Discussion

markquisquirin's avatar
markquisquirin
Frequent Visitor
3 years ago

Adding new column based on another column criteria on the same table

Hello, I have this problem where I wanted to add a new column based on the criteria from another column on the same table. 

Below is what my table looks like. 

NumberLink TypeParty ID
50095RC 
50095AD 
50095COM 
50095SM12345
50095WSM67890

 

Here is the result I wanted to achieve. Add WSM column.

NumberLink TypeParty IDWSM
50095RC 67890
50095AD 67890
50095COM 67890
50095SM1234567890
50095WSM6789067890

 

Is this possible in Power BI? Appreciate your help and response. Thank you.

Let me know if you need more information. 

Thanks,

MarkQ

 

Need Help

12 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    3 years ago
    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjUwsDRV0lEKcgYSCkqxOgghRxcMIWd/XwyxYJCQoZGxiSmKcDhY3MzcwtJAKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Number = _t, #"Link Type" = _t, #"Party ID" = _t]),
    #"Added Custom" = Table.AddColumn(Source, "WSM", each Table.SelectRows(Source,each [Link Type]="WSM"){0}[Party ID])
in
    #"Added Custom"

    How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi markquisquirin ,

    Please have a try.

    Create a measure.

    Measure = MAXX(FILTER(ALL('Table'),'Table'[Number]=SELECTEDVALUE('Table'[Number])),'Table'[Party ID])

    Or a column.

    Column = MAXX(FILTER(ALL('Table'),'Table'[Number]=EARLIER('Table'[Number])),'Table'[Party ID])

    How to Get Your Question Answered Quickly 

     

    If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

     

    Best Regards
    Community Support Team _ Polly

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

    • markquisquirin's avatar
      markquisquirin
      Frequent Visitor
      3 years ago

      Hi v-polly-msft, I am looking for a DAX solution as well, thanks for this. I will try and let you know the result.

      • lbendlin's avatar
        lbendlin
        Icon for Super User rankSuper User
        3 years ago

        if you want to do that in DAX you can use REMOVEFILTERS or otherwise bend the column filter to the WSM value.

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi markquisquirin ,

    Please have a try.

    Create a measure.

    measure =
CALCULATE (
    MAX ( 'Table'[Party ID] ),
    FILTER (
        ALL ( 'Table' ),
        'Table'[Number] = SELECTEDVALUE ( 'Table'[Number] )
            && 'Table'[Link] = "WSM"
    )
)

    Or a column.

    Column =
CALCULATE (
    MAX ( 'Table'[Party ID] ),
    FILTER (
        ALL ( 'Table' ),
        'Table'[Number] = EARLIER ( 'Table'[Number] )
            && 'Table'[Link] = "WSM"
    )
)

    Best Regards
    Community Support Team _ Polly

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

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    3 years ago

    Please specify if you want it as a column or a measure.