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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
eburg
Frequent Visitor

How to return value in a column based on two criteria?

Hello, 

 

I have what I think is a fairly straightforward question that I can't quite execute. I have criteria, in this case ingredient, that are shared across multiple items. I want to look up and return a value based on multiple criteria into another dataset.

 

So I have a dataset that has days worth of results with both Column A and Column C below. So the Ingredient 1 and Ingredient 2 info. I want to pull into that sheet the results from Column E "Output" below.

So when the info in my dataset equals "929062" and "926664" it returns 0.01. And then when it equals "929429" and "926664" it will return whatever is in column E "Output" even though the "92664" is shared between different values in Column A. In this case they're the same but typically they are different (if that helps).

 

example.PNG

1 ACCEPTED SOLUTION
v-zhangti
Community Support
Community Support

Hi, @eburg 

 

You can try the following methods.

Column = 
IF (
    [Ingredient 1] = 929062
        && [Ingredient 2] = 926664,
    0.01,
    IF (
        [Ingredient 1] = 929429
            && [Ingredient 2] = 926664,
        MAX ( 'Table'[Output] ),
        BLANK ()
    )
)

vzhangti_0-1639469248469.png

If the method I provided above can't solve your problem, what's your expected result? Could you please provide more details for it?

 

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
v-zhangti
Community Support
Community Support

Hi, @eburg 

 

You can try the following methods.

Column = 
IF (
    [Ingredient 1] = 929062
        && [Ingredient 2] = 926664,
    0.01,
    IF (
        [Ingredient 1] = 929429
            && [Ingredient 2] = 926664,
        MAX ( 'Table'[Output] ),
        BLANK ()
    )
)

vzhangti_0-1639469248469.png

If the method I provided above can't solve your problem, what's your expected result? Could you please provide more details for it?

 

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

VahidDM
Super User
Super User

Hi @eburg 

 

Try this to add a new column:

Column=
IF([Ingredient 1]=929062&&[Ingredient 2]=926664,0.01,[Output])

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: 
www.linkedin.com/in/vahid-dm/

 

 

 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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