Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
markquisquirin
Frequent Visitor

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

 

12 REPLIES 12
lbendlin
Super User
Super User

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

v-rongtiep-msft
Community Support
Community Support

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

vpollymsft_0-1675130937717.png

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.

v-rongtiep-msft
Community Support
Community Support

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])

vpollymsft_0-1673837156392.png

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.

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.

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

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

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

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

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"

vpollymsft_0-1675045314217.png

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.

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
Super User
Super User

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

Hi Ibendlin, wow! This worked, thanks. 🙂

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.