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.  Number Link Type Party ID 5...
Need Help
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.

markquisquirin's avatar
markquisquirin
Frequent Visitor
3 years ago

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's avatar
    lbendlin
    Icon for Super User rankSuper User
    3 years ago
    WSM DAX = CALCULATE(max(Query1[Party ID]),REMOVEFILTERS(Query1[Party ID]),Query1[Link Type]="WSM")
    • markquisquirin's avatar
      markquisquirin
      Frequent Visitor
      3 years ago

      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: 

      NumberLinkParty ID
      50095RC 
      50095AD 
      50095COM 
      50095SM12345
      50095WSM67890
      50098RC 
      50098AD 
      50098COM 
      50098SM54321
      50098WSM

      9876

       

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

       

      NumberLinkParty IDWSM
      50095RC 67890
      50095AD 67890
      50095COM 67890
      50095SM1234567890
      50095WSM6789067890
      50098RC 9876
      50098AD 9876
      50098COM 9876
      50098SM543219876
      50098WSM9876

      9876

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        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.