Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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 😧

=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
Johanno
Responsive Resident
Responsive Resident

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

 

 

View solution in original post

6 REPLIES 6
Ashish_Mathur
Super User
Super User

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

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

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Johanno
Responsive Resident
Responsive Resident

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

 

 

Anonymous
Not applicable

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
Responsive Resident
Responsive Resident

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.