Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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!
Solved! Go to 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
Proud to be a Super User!
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,
Proud to be a Super User!
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
Proud to be a Super User!
You are an amazing human. I didn't know you could create a variable like that. Thank you thank you thank you!
@Anonymous ,
That is a bit execise :->, but thank you!
Proud to be a Super User!
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
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
Proud to be a Super User!
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.
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...
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
93 | |
87 | |
80 | |
69 | |
68 |
User | Count |
---|---|
226 | |
129 | |
119 | |
83 | |
77 |