New Column: If Statement

[HenryJS]

Hi all,

I want to create a new column. I have two tables - please see below.

The query required is:

• IF 'Export Candidates'[CandidatePayType] = "Umbrella" or "Ltd"
• THEN ('Export Placements'[Rate1ChargeRate] - 'Export Placements'Rate1PayRate')
• *5 IF 'Export Placements'[HourlyOrShiftRate] = "per day"
• *40 IF 'Export Placements'[HourlyOrShiftRate] = "per hour"

Hope that makes sense. Please ask if further details required.

Export Placements

CandidateName	HourlyOrShiftRate	Rate1PayRate	Rate1ChargeRate
John	per day	195	235
Max	per day	250	287.5
Tom	per day	200	240
Ian	per day	200	230
Jerry	per hour	160	219.5
Andrew	per hour	195	229
Phil	per hour	185	219.5

Export Candidates

CandidateName	CandidatePayType
John	PAYE
Max	Ltd
Tom	Umbrella
Ian	Ltd
Jerry	Umbrella
Andrew	Umbrella
Phil	Ltd

Cheers

[v-kelly-msft · Accepted solution]

Hi  @HenryJS .

1. Create a relationship between the 2 tables:

2.Create a measure as below:

Measure = 
var a=MAX('Export Placements'[Rate1ChargeRate])-MAX('Export Placements'[Rate1PayRate])
Return
IF(SELECTEDVALUE('Export Candidates'[CandidatePayType])="Umbrella" || SELECTEDVALUE('Export Candidates'[CandidatePayType])="Ltd",a,
IF(SELECTEDVALUE('Export Placements'[HourlyOrShiftRate])="per day",a*5,
IF(SELECTEDVALUE('Export Placements'[HourlyOrShiftRate])="per hour",a*40,BLANK())))

Finally you will see:

For the related .pbix file,pls click here.

Best Regards,
Kelly

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

[BA_Pete]

Hi @v-qiuyu-msft 

Could you kindly explain why you have chosen the solution that you have as solving the OP's question please? It seems that there are other posts that provide solutions more in line with the OP's requirements and display outcomes as the OP described. The solution accepted does not appear to show the desired output.

Thanks.

[Pragati11]

Hi @HenryJS ,

Have you got any relationship in place between these 2 tables?

Also, your last 2 conditions don't have an outcome against it as highlighted below:

• IF 'Export Candidates'[CandidatePayType] = "Umbrella" or "Ltd"
• THEN ('Export Placements'[Rate1ChargeRate] - 'Export Placements'Rate1PayRate')
• *5 IF 'Export Placements'[HourlyOrShiftRate] = "per day"
• *40 IF 'Export Placements'[HourlyOrShiftRate] = "per hour"

Also, what does *5 and *40 signify here?

Thanks,

Pragati

[HenryJS]

Hi @Pragati11 

*5 and * 40 signify x 5 and x 40 respectively

So,

IF 'Export Placements'[HourlyOrShiftRate] = "per day"

THEN ('Export Placements'[Rate1ChargeRate] - 'Export Placements'Rate1PayRate') *5

and 

IF 'Export Placements'[HourlyOrShiftRate] = "per hour"

THEN ('Export Placements'[Rate1ChargeRate] - 'Export Placements'Rate1PayRate') *40

[Pragati11]

Hi @HenryJS ,

I am assuming following:

1. You have got a relationship in place between these 2 tables.
2. 'Export Placements'[HourlyOrShiftRate] has only 2 unique values: "per day" and "per hour"

Based on the above assumptions, You can create a column using following DAX expression:

Calc_Column = IF (

                             'Export Candidates'[CandidatePayType] = "Umbrella" ||  'Export Candidates'[CandidatePayType] = "Ltd", 

                             'Export Placements'[Rate1ChargeRate] - 'Export Placements'Rate1PayRate',

                              IF('Export Placements'[HourlyOrShiftRate] = "per day",

                                  ('Export Placements'[Rate1ChargeRate] - 'Export Placements'Rate1PayRate') *5,

                                  ('Export Placements'[Rate1ChargeRate] - 'Export Placements'Rate1PayRate') *40

                                 )

                            )

If this helps and resolves the issue, appreciate a Kudos and mark it as a Solution! 🙂

Thanks,

Pragati

[BA_Pete]

Hi @HenryJS 

Please try this measure (assuming relationship between the two tables - the data you provided gives 1:1 relationship on name):

_measure = 
VAR paytype =
    MAX(ExportCandidates[CandidatePayType])
VAR shifttype =
    MAX(ExportPlacements[HourlyOrShiftRate])
RETURN
    IF(
        paytype = "Umbrella" || paytype = "Ltd",
        SUMX(
            ExportPlacements,
            ExportPlacements[Rate1ChargeRate] - ExportPlacements[Rate1PayRate]
        ) *
        SWITCH(
            TRUE(),
            shifttype = "per day", 5,
            shifttype = "per hour", 40
        ),
        0
    )

This gives me the following results:

Pete