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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

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
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors