Forum Discussion
Converting a basic Excel formula into DAX for a new column in Power BI
I'm trying to create a new column in Power BI Desktop which replicates a basic Excel formula which seems simple enough.
The Excel formula which I am trying to replicate is in column D:
=IF(B2=B1, 0, C2)
So that the returned column should look like column D.
I have the same data structure in Power BI and would like to create a new column to replicate column D in Excel, but I can't find a way to replicate the formula where it calls for :
B2=B1
I would be grateful for anyone's help.
Many thanks in advance! :smileyhappy:
You could shift one column using:
Shifted column = LOOKUPVALUE(Table1[Cage No];Table1[Index Order];Table1[Index Order]-1)
or you could get the result directly by using:
Reported size = IF(Table1[Cage No]=LOOKUPVALUE(Table1[Cage No];Table1[Index Order];Table1[Index Order]-1);0;Table1[Size])
6 Replies
- Good question. After searching I think you would have to first create a new column that is shifted one level from the first. Then you can check which rows are equal. Those rows should return blanks on the rest the size. This seems similar:
https://community.powerbi.com/t5/Desktop/How-to-subtract-current-row-from-prior-row-and-so-on-in-power-bi/td-p/179806
Let us know if it works. You could shift one column using:
Shifted column = LOOKUPVALUE(Table1[Cage No];Table1[Index Order];Table1[Index Order]-1)
or you could get the result directly by using:
Reported size = IF(Table1[Cage No]=LOOKUPVALUE(Table1[Cage No];Table1[Index Order];Table1[Index Order]-1);0;Table1[Size])
Hi,
This can also be solved with the following M code
let
Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Index Order", Int64.Type}, {"Cage No", type text}, {"Size", Int64.Type}}),
Partition = Table.Group(#"Changed Type", {"Cage No"}, {{"Partition", each Table.AddIndexColumn(_, "Index",1,1), type table}}),
#"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"Index Order", "Size", "Index"}, {"Index Order", "Size", "Index"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Expanded Partition",{{"Index", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type1", "Custom", each if [Index]=1 then [Size] else 0),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index"})
in
#"Removed Columns"You are welcome. If my reply helps, please mark it as Answer.
