Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
yforti
Helper II
Helper II

Help with Dax Formula

Hi guys!


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

1.PNG
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?
1 REPLY 1
dk_dk
Super User
Super User

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:

dk_dk_0-1699978460501.png


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.








Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors