Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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
Please specify if you want it as a column or a measure.
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.
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.
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. 😞
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 |
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.
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.
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. 🙂
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
89 | |
86 | |
82 | |
64 | |
49 |
User | Count |
---|---|
125 | |
111 | |
88 | |
69 | |
66 |