Forum Discussion

Daniff's avatar
Daniff
Helper I
5 years ago

Vba To power bi

Hello, please help me. I want convert this vba code for use on power bi with dax. Please help me. Many thanks

 

Option Explicit

Public Function Trocar(Cat As String, Produto As String, PVP As Double, Rentab As Double) As String
'(c) Vlamir Pereira - jan2021
'www.youtube.com/c/excelfutura
Dim Lin As Long
Dim PVPLimite As Double
Dim PVPAtual As Double
Dim Trocou As Boolean
Trocou = False
Lin = 3
If Application.WorksheetFunction.CountIfs(Sheets("TABELA1").Range("B:B"), Produto) > 0 Then

Trocar = "Parceiro"
Exit Function
End If
Trocar = "NO SWITCH"
Cat = UCase(Cat)
PVPLimite = PVP + 1
PVPAtual = PVP
Do While Sheets("TABELA1").Cells(Lin, 1) <> ""
If UCase(Sheets("TABELA1").Cells(Lin, 1)) = Cat Then

If Sheets("TABELA1").Cells(Lin, 3) <= PVPLimite And Sheets("TABELA1").Cells(Lin, 4) > Rentab Then

Trocar = Sheets("TABELA1").Cells(Lin, 2)
Rentab = Sheets("TABELA1").Cells(Lin, 4)
PVPAtual = Sheets("TABELA1").Cells(Lin, 3)
Trocou = True
ElseIf Sheets("TABELA1").Cells(Lin, 3) <= PVPLimite And Sheets("TABELA1").Cells(Lin, 4) = Rentab And Trocou Then

If PVPAtual > PVP And PVPAtual > Sheets("TABELA1").Cells(Lin, 3) Then

Trocar = Sheets("TABELA1").Cells(Lin, 2)
Rentab = Sheets("TABELA1").Cells(Lin, 4)
PVPAtual = Sheets("TABELA1").Cells(Lin, 3)
ElseIf PVPAtual < PVP And PVPAtual < Sheets("TABELA1").Cells(Lin, 3) And PVP > Sheets("TABELA1").Cells(Lin, 3) Then

Trocar = Sheets("TABELA1").Cells(Lin, 2)
Rentab = Sheets("TABELA1").Cells(Lin, 4)
PVPAtual = Sheets("TABELA1").Cells(Lin, 3)
End If
End If
End If
Lin = Lin + 1
Loop
End Function

Need Help

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Daniff,

    I don't think you can use DAX expressions to reproduce the 'do while loop' part. (search value, if statement, compare value parts and output the result are supports)
    Power bi data model table does not include the row and column index. For the reference operation to the specific cells, you need to add filters to reduce the result ranges to point to the specific values.
    For the DAX iterator functions, they are looping on the parameter table rows instead of looping in the range with particular exit conditions. 

    BTW, the recursive calculation also not supported in dax expressions.

    Regards,

    Xiaoxin Sheng