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'[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

CandidateNameHourlyOrShiftRateRate1PayRateRate1ChargeRate
Johnper day195235
Maxper day250287.5
Tomper day200240
Ianper day200230
Jerryper hour160219.5
Andrewper hour195229
Philper hour185219.5

 

 

Export Candidates

 

CandidateNameCandidatePayType
JohnPAYE
MaxLtd
TomUmbrella
IanLtd
JerryUmbrella
AndrewUmbrella
PhilLtd

 

 

Cheers

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!

6 Replies

  • Pragati11's avatar
    Pragati11
    Super User
    6 years ago

    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'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

       

      • BA_Pete's avatar
        BA_Pete
        Super User
        6 years ago

        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

  • v-kelly-msft's avatar
    v-kelly-msft
    Community Support
    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!
    • BA_Pete's avatar
      BA_Pete
      Super User
      6 years ago

      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.