Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

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=...
Need Help
  • Johanno's avatar
    Johanno
    8 years ago

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

     

     

Ashish_Mathur's avatar
Ashish_Mathur
Icon for Super User rankSuper User
8 years ago

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's avatar
    Anonymous
    Not applicable
    8 years ago
    Thank you for your additional suggestion. I will also give this a go :)
    • Ashish_Mathur's avatar
      Ashish_Mathur
      Icon for Super User rankSuper User
      8 years ago

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