Forum Discussion

yforti's avatar
yforti
Helper II
2 years ago

Help with Dax Formula

Hi guys!


I'm having trouble building a dax formula.
The problem is the following:


The NrPedido column is made up of unique numbers, these numbers are repeated for each step in the Host Service column, and I need each NrPedido number that contains step "sendResquestPayment" to be marked in the column created in all lines.

So far I have made the following formula:

Coluna =
CALCULATE(
    MAX(
        iFrame[NroPedido2]),
        iFrame[NroPedido2] = EARLIER(iFrame[NroPedido2]),
        iFrame[HostService] = "sendResquestPayment")

As you can see, it only marks the line that contains step "sendResquestPayment", however, I would like it to mark for all lines of NrPedido, to indicate that that order contains this step as a whole.

Can u guys help me?
Need Help
Tips and Tricks
Tutorial Requests

1 Reply

  • dk_dk's avatar
    dk_dk
    Super User
    2 years ago

    Hi yforti ,

    Please see if this solution works for you:

    1, I created a sample table for myself similar to your data:
    HostServiceNrPedido

    sendPayment123
    otherStep123
    anotherStep123
    otherStep456
    receiveParkingTicket456
    powerBIisnice456
    yetanotherStep789
    sendPayment789
    otherStep789
    noPayment4U000


    The NrPedido column is formatted as general, and set to not summarize. The table is called "Eventos" in my example.

    Then I created a calculated column like this:

    sendPaymentStep = IF(Eventos[HostService]="sendPayment",Eventos[HostService] & Eventos[NrPedido],"")

    This will have the concatenated value of the host service and nr pedido columns, only if the hostservice = "sendPayment" - you can change this string to the exact string you want to check for.

    Then I made another calculated column which will reference the previous one:

    FindPaymentInHostService = 

LOOKUPVALUE(Eventos[NrPedido],Eventos[sendPaymentStep],"sendPayment"&Eventos[NrPedido])

    It will return the NrPedido column, if it can find in the sendPaymentStep column the string "sendPayment"+whatever the NrPedido is in the row context. You need to change "sendPayment" here as well to the exact string you want to check for.

    My result then looks like this:


    For every NrPedido regardless of the HostService, it will show the NrPedido in the row only if at least one of the steps is "sendPayment". If none of the steps are "sendPayment" then the value will be blank.

    I hope this helps, let me know if this is not the intended behavior you are after.