If statement

[Anonymous]

Hi everyone, 

i am having a hardtime translating this easy vba code to dax :

If Right(Left(v(i, 11), 3), 2) = "13" Or Right(Left(v(i, 11), 3), 2) = "35" Then
v(i, 33) = 1 

Else

If (Len(v(i, 19)) < 5) Then
v(i, 33) = "" 

Else

If (Len(v(i, 14)) < 5) Then

   If (Len(v(i - 1, 14)) < 5) And v(i, 2) = v(i - 1, 2) Then
   v(i, 33) = v(i - 1, 33)

Else

If DateValue(v(i, 19)) = DateValue(Now) Then
v(i, 33) = 1 

Else

If DateValue(v(i, 19)) > DateValue(Now) Then 
v(i, 33) = 1 

End If
Else
v(i, 33) = 0 

End If
End If
End If
Else

If DateValue(v(i, 14)) = DateValue(v(i, 19)) Then
v(i, 33) = 1 

Else

If DateValue(v(i, 14)) < DateValue(v(i, 19)) Then
v(i, 33) = 1

End If
Else
v(i, 33) = 0 

End If
End If
End If
End If
End If

[Pragati11]

HI @Anonymous ,

Just sharing the VBA code here doesn't help us in understanding what you are trying to achieve without even giving the description around data and what calculation is required.

Add more details: https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Also while pasting code in anyother language, always consider not everyone is expert with that language 🙂

Thanks,

Pragati

[Anonymous]

Yes sorry about that i am new here, here is a visual of the table (the last column is the column that i am trying to create otd_in). I am going to try to be brief with this : 

If the value of post (far left) ends with 35 or 13 then OTD_IN = 1 else

    if date_deb_prev is empty then otd_in = " "

Else

if dt_deb is empty then

if dt_deb of the earlier row is equal to current row AND the orfa of the earlier row is equal to current row THEN otd_in = next (otd_in)

Else

if dt_deb_prev = today() then otd_in =1

Else 

if dt_deb_prev > today() then otd_in =1

End If

else otd_in = 0

end if end if end if

else

if dt_deb = date_deb_prev then otd_in = 1 else

if dt_deb < date_deb_prev then otd_in = 1

end if

Else otd_in =0

[ERD]

@Anonymous ,

please, explain what you are expecting to see here: "otd_in = next (otd_in)".

If this post helps, then please consider Accept it as the solution ✔️to help the other members find it more quickly.

[Anonymous]

what i meant is the otd of  row x = otd of row x +1. This line right here is where i am really stuck because you cannot use the calculated column name in the dax code since power BI doesn't recognize it yet.

[v-jingzhang · Accepted solution]

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.

[Anonymous]

Thank you so much  v-jingzhang, works like a charm and it was very educational because i tried it with M and it didn't work but now i have a pretty idea how the langage works, just awesome thank you again!!!

[v-jingzhang]

You are welcome. I often feel M is more powerful than I can imagine. Also learnt a lot from other posts and blogs. Have fun with M!😉