cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## How to create a new column based on criteria in other columns?

I have been acquainting myself with Power BI for about a day now, so you can say I'm a complete novice.

Below are several different companies' price proposals.  I want to create a new column to indicate only the "bid amount" if that company was low bidder on the project.  The idea is that I could then perform analytics on the revenue of each company.  How would I go about doing this?

For example, in the first two rows, I would want a new column that indicates \$6078976.36 for "Bidder X" (I drew in the letters) since they were low, and \$0 for "Bidder Y," since they lost.  Please note that there are several different "Proposals" in this table, I only screenshotted this particular proposal "135" but I'd want to do this for every proposal in my data set.

Essentially, I want to create a new column whose value is based on the minimum bid placed on a given proposal.  All other bidders would have a value of 0 for that proposal.

Thank you!

1 ACCEPTED SOLUTION
Microsoft Employee

Hi @Anonymous ,

Solution 1 based on Power Query.

let currentProposal = [Proposal], currentBid = [Bid Amount]
in
if List.Min(Table.SelectRows(#"Changed Type", each [Proposal] = currentProposal)[Bid Amount]) = currentBid
then currentBid
else 0

Solution 2 based on DAX.

Column =
IF (
Table1[Bid Amount]
= CALCULATE ( MIN ( Table1[Bid Amount] ), ALLEXCEPT ( Table1, Table1[Proposal] ) ),
Table1[Bid Amount],
0
)

Best Regards,

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Microsoft Employee

Hi @Anonymous ,

Solution 1 based on Power Query.

let currentProposal = [Proposal], currentBid = [Bid Amount]
in
if List.Min(Table.SelectRows(#"Changed Type", each [Proposal] = currentProposal)[Bid Amount]) = currentBid
then currentBid
else 0

Solution 2 based on DAX.

Column =
IF (
Table1[Bid Amount]
= CALCULATE ( MIN ( Table1[Bid Amount] ), ALLEXCEPT ( Table1, Table1[Proposal] ) ),
Table1[Bid Amount],
0
)

Best Regards,

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.