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

    • 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