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

[markquisquirin]

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. 

Number	Link Type	Party ID
50095	RC
50095	AD
50095	COM
50095	SM	12345
50095	WSM	67890

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

Number	Link Type	Party ID	WSM
50095	RC		67890
50095	AD		67890
50095	COM		67890
50095	SM	12345	67890
50095	WSM	67890	67890

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

Let me know if you need more information. 

Thanks,

MarkQ

[lbendlin]

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

[Anonymous]

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.

[Anonymous]

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]

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]

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

[markquisquirin]

Hi Ibendlin, wow thanks. Would you be able to show me how I can do that? I tried REMOVEFILTERS before, but it didn't work. 😞 

[lbendlin]

WSM DAX = CALCULATE(max(Query1[Party ID]),REMOVEFILTERS(Query1[Party ID]),Query1[Link Type]="WSM")

[markquisquirin]

Sorry for the late reply. thanks for this. 🙂 

I tried this, but it gives me an error - "A single value for column 'Link Type' in Table Query1 cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."

Here's how my table looks like: 

Number	Link	Party ID
50095	RC
50095	AD
50095	COM
50095	SM	12345
50095	WSM	67890
50098	RC
50098	AD
50098	COM
50098	SM	54321
50098	WSM	9876

and I was hoping to look like this.... thanks

Number	Link	Party ID	WSM
50095	RC		67890
50095	AD		67890
50095	COM		67890
50095	SM	12345	67890
50095	WSM	67890	67890
50098	RC		9876
50098	AD		9876
50098	COM		9876
50098	SM	54321	9876
50098	WSM	9876	9876

[Anonymous]

Hi @markquisquirin ,

Please have a try.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjUwsDRV0lEKcgYSCkqxOgghRxcMIWd/XwyxYJCQoZGxiSmKcDhY3MzcwtIALm6BaZEFpkUWWCyygFlkamJsZIgiDLHI0sLcTCk2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Number = _t, Link = _t, #"Party ID" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Number", Int64.Type}, {"Link", type text}, {"Party ID", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Table.SelectRows(#"Changed Type",(x)=>x[Number]=[Number] and x[Link] = "WSM")[Party ID]),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded Custom",{{"Custom", "WSM"}})
in
    #"Renamed Columns"

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjUwsDRV0lEKcgYSCkqxOgghRxcMIWd/XwyxYJCQoZGxiSmKcDhY3MzcwtIALm6BaZEFpkUWWCyygFlkamJsZIgiDLHI0sLcTCk2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Number = _t, Link = _t, #"Party ID" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Number", Int64.Type}, {"Link", type text}, {"Party ID", Int64.Type}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Link] = "WSM")),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Number"}, #"Filtered Rows", {"Number"}, "Filtered Rows", JoinKind.LeftOuter),
    #"Expanded Filtered Rows" = Table.ExpandTableColumn(#"Merged Queries", "Filtered Rows", {"Party ID"}, {"Party ID.1"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded Filtered Rows",{{"Party ID.1", "WSM"}})
in
    #"Renamed Columns"

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]

Thanks, Community Support Team _ Polly, but I was looking for a DAX solution. Do you have a way I can do this in DAX? Thanks. 

[lbendlin]

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".

[markquisquirin]

Hi Ibendlin, wow! This worked, thanks. 🙂