Create a custom column using an IF statement

[dreaves1]

Hi All,

I'm trying to create a custom column for offers rejected/rescinded out of two columns, Offers and Hires. I want to substract the number of Offers by Hires if the number of Offers is more than the number of Hires. If the number of Offers is least than the number of Hires, I want to keep the number in the Offers field. Can someone please demonstrate how this can be accomplished? 

[harshnathani · Accepted solution]

Hi @dreaves1 ,

Create a new Column

New Column =
SWITCH (
    TRUE (),
    'Table'[Offers] > 'Table'[Hires], 'Table'[Offers] - 'Table'[Hires],
    'Table'[Offers] < 'Table'[Hires], 'Table'[Offers],
    BLANK ()
)

Regards,

Harsh Nathani

Appreciate with a Kudos!! (Click the Thumbs Up Button)

Did I answer your question? Mark my post as a solution!

[harshnathani]

HI @dreaves1 ,

You need to create this in Power BI Desktop.

Regards,

Harsh Nathani

[dreaves1]

It won't recognize my table now and I get an error message. 

[v-lionel-msft]

Hi @dreaves1 ,

Or create a conditional column in 'Power Query'.

Best regards,
Lionel Chen

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

[harshnathani]

For a custom Column Formula in Power Query you can use

If [Offers] > [Hires] then [Offers] - [Hires] else [Offers]

Regards,

HN

[harshnathani]

Hi @dreaves1 ,

Follow these steps

Regards,

Harsh Nathani

Appreciate with a Kudos!! (Click the Thumbs Up Button)

Did I answer your question? Mark my post as a solution!

[Greg_Deckler]

@dreaves1 - Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

That being said, the syntax for IF statements is:

DAX: IF([Column1] = "Something" || [Column 2] = "Something else",true,false)

You can use && and || for logical AND and OR statements.

Power Query: if [Column1] = "Something" then "True" else "False"

[ryan_mayu]

@dreaves1 

please provide the sample data and expected output.