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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Resolver IV
Resolver IV

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.





Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.