Forum Discussion

HenryJS's avatar
HenryJS
Icon for Post Prodigy rankPost 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
Icon for Post Prodigy rankPost 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
Icon for Super User rankSuper 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