If statement

Hi Anonymous

 

In Power BI Desktop, there is no row number in DAX for us to refer to earlier or next row easily like in Excel. As an alternative, you can try adding a custom column in Power Query Editor instead of a calculated column with DAX. That is possible. 

 

Steps in Power Query Editor are:

1. Add an Index column starts with 0. This will be used like row index in the following steps.

 

2. Add a custom column with below codes. I don't cover all "if else" conditions of your requirement. You can add other conditions into it. #"Added Index" is the previous step name. Here I use 5 (a value doesn't equal to 0 or 1) temporarily to deal with the troublesome part.

let previousRow = #"Added Index"{[Index]-1} in
        if Text.Middle([post],1,2) = "13" or Text.Middle([post],1,2) = "35" then 1
        else if [de_deb] = null then 
            if [de_deb] = previousRow[de_deb] and [orfa] = previousRow[orfa] then 5 
            else if [date_deb_prev] >= DateTime.LocalNow() then 1 
            else 0 
        else 0

 

3. After all other [OTD_IN] values are filled correctly with 0 or 1, replace 5 with null.

 

4. Use FillDown to copy down the value from the cell above. Or use FillUp to use the value from the cell below. 

 

Here are all codes you can paste into a blank query's Advanced editor to look at all steps.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lY6xDcAgDARXiahBvG0MmAUyRJT91whYSpEiBd19cae/rnAyJZSupYcYWMyaLKKCtSuSMk+qWZCZjj6gPqmtiQGEO/5n9M2QZ3bU6qrKfIA9lZurosnMJrXcl0Y6/M43cz8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [grof = _t, orfa = _t, phase = _t, post = _t, de_deb = _t, date_deb_prev = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"grof", type text}, {"orfa", Int64.Type}, {"phase", Int64.Type}, {"post", type text}, {"de_deb", type datetime}, {"date_deb_prev", type datetime}}),
    // Add an Index column which will work like row number
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    // previousRow is a record value
    #"Added Custom" = Table.AddColumn(#"Added Index", "OTD_IN", each let previousRow = #"Added Index"{[Index]-1} in
        if Text.Middle([post],1,2) = "13" or Text.Middle([post],1,2) = "35" then 1
        else if [de_deb] = null then 
            if [de_deb] = previousRow[de_deb] and [orfa] = previousRow[orfa] then 5 
            else if [date_deb_prev] >= DateTime.LocalNow() then 1 
            else 0 
        else 0),
    // replace 5 with null
    #"Replaced Value" = Table.ReplaceValue(#"Added Custom",5,null,Replacer.ReplaceValue,{"OTD_IN"}),
    // fill down
    #"Filled Down" = Table.FillDown(#"Replaced Value",{"OTD_IN"})
in
    #"Filled Down"

 

Also attach the pbix for your reference. Hope this helps.

 

Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.