Forum Discussion
How to perform a function only for a unique value from a column
Hi, i would like to get values only for unique values in the column in Power BI.
This is an example of my current table, in the backlog I used the function:
| NUMBER | PLANNEDDATE | REALDATE | BACKLOG |
| 1234 | 04.07.22 | 11.07.22 | -6 |
| 1234 | 04.07.22 | 11.07.22 | -6 |
| 0000 | 04.07.22 | 14.07.22 | -9 |
| 9999 | 04.07.22 | 08.07.22 | -5 |
| 9999 | 04.07.22 | 08.07.22 | -5 |
| 9999 | 04.07.22 | 08.07.22 | -5 |
and I would like to achieve something like this
| NUMBER | PLANNEDDATE | REALDATE | BACKLOG |
| 1234 | 04.07.22 | 11.07.22 | -6 |
| 1234 | 04.07.22 | 11.07.22 | |
| 0000 | 04.07.22 | 14.07.22 | -9 |
| 9999 | 04.07.22 | 08.07.22 | -5 |
| 9999 | 04.07.22 | 08.07.22 | |
| 9999 | 04.07.22 | 08.07.22 |
if the number repeats, I don't want to count the backlog for it.
How i can do it?
You could add an index column that restarts with each new NUMBER, and then add a new column with your formula conditional on the index.
See Create Row Number for Each Group in Power BI using Power Query - RADACAD.
Steps would include grouping by NUMBER, keeping all rows, adding an index column, then expanding.
Then you could just add your column with the Custom Formula option in the UI.
Hi,
i was working on the same schema as karen578
this is the transformations in power query
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyNlHSUTIw0TMw1zMyAjINDeFMXTOlWB3i1BgAAZoaBFPXEqzGEghQ1RhYINSYUk9NLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [NUMBER = _t, PLANNEDDATE = _t, REALDATE = _t, BACKLOG = _t]),
#"Grouped Rows" = Table.Group(Source, {"NUMBER"}, {{"AllRows", each _, type table [NUMBER=nullable text, PLANNEDDATE=nullable text, REALDATE=nullable text, BACKLOG=nullable text]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([AllRows],"Index",1,1)),
#"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
#"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"NUMBER", "PLANNEDDATE", "REALDATE", "BACKLOG", "Index"}, {"NUMBER", "PLANNEDDATE", "REALDATE", "BACKLOG", "Index"}),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Custom",{{"NUMBER", type text}, {"PLANNEDDATE", type date}, {"REALDATE", type date}, {"BACKLOG", Int64.Type}, {"Index", Int64.Type}})
in
#"Changed Type"and finally the dax
Column =if('Table'[Index]=1, 'Table'[BACKLOG])If this post is useful to help you to solve your issue, consider giving the post a thumbs up and accepting it as a solution !
3 Replies
You could add an index column that restarts with each new NUMBER, and then add a new column with your formula conditional on the index.
See Create Row Number for Each Group in Power BI using Power Query - RADACAD.
Steps would include grouping by NUMBER, keeping all rows, adding an index column, then expanding.
Then you could just add your column with the Custom Formula option in the UI.
Hi,
i was working on the same schema as karen578
this is the transformations in power query
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyNlHSUTIw0TMw1zMyAjINDeFMXTOlWB3i1BgAAZoaBFPXEqzGEghQ1RhYINSYUk9NLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [NUMBER = _t, PLANNEDDATE = _t, REALDATE = _t, BACKLOG = _t]),
#"Grouped Rows" = Table.Group(Source, {"NUMBER"}, {{"AllRows", each _, type table [NUMBER=nullable text, PLANNEDDATE=nullable text, REALDATE=nullable text, BACKLOG=nullable text]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([AllRows],"Index",1,1)),
#"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
#"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"NUMBER", "PLANNEDDATE", "REALDATE", "BACKLOG", "Index"}, {"NUMBER", "PLANNEDDATE", "REALDATE", "BACKLOG", "Index"}),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Custom",{{"NUMBER", type text}, {"PLANNEDDATE", type date}, {"REALDATE", type date}, {"BACKLOG", Int64.Type}, {"Index", Int64.Type}})
in
#"Changed Type"and finally the dax
Column =if('Table'[Index]=1, 'Table'[BACKLOG])If this post is useful to help you to solve your issue, consider giving the post a thumbs up and accepting it as a solution !