Forum Discussion
How to dynamically change column values based on other columns?
Hi all,
I would sincerely some advice with the following challenge.
In the following data set, "0" implies that the item is sold, while "1" implies that the item is still on the shelf. The expected scenario is:
- User periodically reviews the table and changes Item status if it has been sold
- If some items have been sold, user changes item status from „1“ to „0“
- Power BI would automatically change the Item status from „1“ to „0“ for the rest of the items with matching names.
Example: today user has changed the Item status for item A to „0“. I would like to write a function that would change Item status from „1“ to „0“ in other rows too, where Item = A.
Is it possible to do that in Power BI?
| Item | Item status (0 = sold; 1 = on the shelf) | Update date |
A | 0 | 2021-05-22 |
| A | 1 | 2021-04-22 |
| A | 1 | 2021-03-22 |
| B | 1 | 2021-05-22 |
| B | 1 | 2021-04-22 |
| B | 1 | 2021-03-22 |
| C | 1 | 2021-05-22 |
Anonymous
If you want to add a new column to your table with the updated status, add the following as a new columnNew Status = CALCULATE( MIN(Table4[Status]), ALLEXCEPT(Table4,Table4[Item] ) )Hi Anonymous ,
In dax, you need to create a new column as mentioned by Fowmy, But you can use Table.replacevalue to replace 1 to 0.
Original data:
Then you can use the following m query:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIAYiMDI0NdA1NdIyOlWB2IsCFc2AS7sDFM2AlF2BS7sAl2YbghzlgMiQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Item = _t, #"Item status" = _t, #"Update date" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Item", type text}, {"Item status", Int64.Type}, {"Update date", type date}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Item"}, {{"Count", each if List.Contains(_[Item status],0) then Table.ReplaceValue(_,1,0,Replacer.ReplaceValue,{"Item status"}) else _}}), #"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"Item status", "Update date"}, {"Count.Item status", "Count.Update date"}) in #"Expanded Count"Please refer to pbix file.
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Dedmon Dai
3 Replies
- Fowmy
Super User
Anonymous
If you want to add a new column to your table with the updated status, add the following as a new columnNew Status = CALCULATE( MIN(Table4[Status]), ALLEXCEPT(Table4,Table4[Item] ) ) 
v-deddai1-msftCommunity Support
Hi Anonymous ,
In dax, you need to create a new column as mentioned by Fowmy, But you can use Table.replacevalue to replace 1 to 0.
Original data:
Then you can use the following m query:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIAYiMDI0NdA1NdIyOlWB2IsCFc2AS7sDFM2AlF2BS7sAl2YbghzlgMiQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Item = _t, #"Item status" = _t, #"Update date" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Item", type text}, {"Item status", Int64.Type}, {"Update date", type date}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Item"}, {{"Count", each if List.Contains(_[Item status],0) then Table.ReplaceValue(_,1,0,Replacer.ReplaceValue,{"Item status"}) else _}}), #"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"Item status", "Update date"}, {"Count.Item status", "Count.Update date"}) in #"Expanded Count"Please refer to pbix file.
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Dedmon Dai
Hi v-deddai1-msft it works, but this looks quite complicated to honest:) many thanks for the alternative option!