cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
misskayanne Frequent Visitor
Frequent Visitor

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.  

Excel SS.JPG

 

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

PBI SS.JPG

 

I would be grateful for anyone's help. 

 

Many thanks in advance! Smiley Happy

1 ACCEPTED SOLUTION

Accepted Solutions
Johanno Member
Member

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

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

Skärmklipp.JPG

 

 

6 REPLIES 6
Highlighted
Johanno Member
Member

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

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.
Johanno Member
Member

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

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

Skärmklipp.JPG

 

 

misskayanne Frequent Visitor
Frequent Visitor

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

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!
Super User
Super User

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

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"

 

Untitled.png

misskayanne Frequent Visitor
Frequent Visitor

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

Thank you for your additional suggestion. I will also give this a go Smiley Happy
Super User
Super User

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

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