Converting a basic Excel formula into DAX for a new column in Power BI

[Anonymous]

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 😧

=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! 

[Ashish_Mathur]

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"

[Anonymous]

Thank you for your additional suggestion. I will also give this a go 🙂

[Ashish_Mathur]

You are welcome.  If my reply helps, please mark it as Answer.

[Johanno · Accepted solution]

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

[Anonymous]

Thank you so much, this is exactly what I was looking for. I've been trying to figure out how to use the index order column to solve this for a whole week. Thank you!

[Johanno]

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

Let us know if it works.