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