If with LookUpValue returning a value based on the entire table

[Anonymous]

Hi everyone,

I am looking to create a new column that returns "Won" or "Loss" based on three conditions. The issue with just making a new column like that is the the table I am working with has two files in it. A Quotes file and Sales. The quotes file has 47,000 records and the sales file has 840,000 records. 

I cannot compare the columns as they are not lined up by row and do not match.

Above is an example of the data I have where there are many null values for the Quote side of the table, but the sales side is very full. The DesiredOutput column is what I want as an example.

If QuoteCustomer = SalesCustomer, QuoteProduct = SalesProduct, QuoteQuantity = SalesQuantity, "Won","Loss"

The if function above, syntax is not correct I know, is what I am trying to compare across the entire table.

I am thinking an LookUpValue function with an if condition would work as I need to base it off the entire table rather than row-by-row. 

Thank you!

[richbenmintz]

Hi @Anonymous ,

Are you able to share a sample pbix file with the two tables that need to be compared? or at least some sample data.

Thanks,

[richbenmintz · Accepted solution]

Hi @Anonymous,

The following Calculated column will get you what you need, where your data is in a single table

Desired Output = 
var _cust = 'sample'[SalesCustomer]
var _prod = 'sample'[SalesProduct]
var _qty = 'sample'[SalesQuantity]
return 
if(
    CALCULATE(COUNTROWS('sample'), FILTER(ALL('sample'), _cust = 'sample'[QuoteCustomer] && _prod = 'sample'[QuoteProduct] && _qty = 'sample'[QuoteQuantity]))>0,
     "Won",
      "Lost"
    )

Hope that Helps

[Anonymous]

You are an amazing human. I didn't know you could create a variable like that. Thank you thank you thank you!

[richbenmintz]

@Anonymous ,

That is a bit execise :->, but thank you!

[Anonymous]

@richbenmintz 

It is now one table and the picture I included in the post is an example of some sample data I have. The last column is a the column I am looking to create 

[richbenmintz]

Hi @Anonymous ,

Including the data as a table rather than an image makes it a whole lot easier to create a mockup, give me a couple minutes and I Can get a formula for you

[Anonymous]

Oh that makes a lot more sense. I'm sorry about that. I'm new to Power BI and the forums. I will keep that in mind in the future. 

[RicFischer]

You may want to take a look at the top post in the forum. It has some good tips on getting faster answers to your questions (because you're making it easier for people answer):

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/td-p/1447523/jump...