Forum Discussion

HenryJS's avatar
HenryJS
Post Prodigy
6 years ago
Solved

New Column: If Statement

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'...
Data Protection
General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • v-kelly-msft's avatar
    v-kelly-msft
    6 years ago

    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!
HenryJS's avatar
HenryJS
Post Prodigy
6 years ago

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's avatar
Pragati11
Super User
6 years ago

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