Forum Discussion

ihartdata's avatar
ihartdata
Microsoft Employee
7 years ago
Solved

Calculated Column with Nested IF Statement, Use Contains, Search, Find??

I am trying to create a calculated column for a new triage status. So Far I have the following DAX, which covers bullets 1, 2 & 3 but I'm having a difficult time with the 4th and 5th bullets.  I just can't figure out what I should be using... 

 

ReviewStatus = IF([FeatureStatus]="Generally Available", "GA", IF(OR([FeatureStatus]="Rejected",[FeatureStatus]="Rejected - Workaround Available"),"Rejected",IF(OR([FeatureStatus]="Private Preview",[FeatureStatus]="Public Preview"),"In Preview", ??????

 

  1. GA: Feature Status IS "Generally Available"
  2. InPreview:  Feature Status IS "Private Preview" OR "Public Preview"
  3. Rejected:  Feature Status IS "Rejected" OR "Rejected – Workaround Available"
  4. Not Planned: Feature Status IS NOT "Rejected", "Rejected – Workaround Available", "Generally Available", "Public Preview" OR "Private Preview" AND Semester Planning IS "No Plan"
  5. Planned: Feature Status IS NOT "Generally Available", "Rejected", "Rejected – Workaround Available" OR "Evidence Gathering" AND Semester Planning IS NOT "No Plan"

I have two tables:

 

Table 1: 'Features' and it has a column 'Feature Status'

Table 2: 'Release Lookup' and it has a column 'Semester Planning' 

 

These tables are related to eachother. 

Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    ihartdata 
    I think this may assist.
    First custom column

    Semester Planning =
    LOOKUPVALUE (
    'Release Lookup'[Semester Planning],
    'Release Lookup'[ReleaseTime], Features[ReleaseTime]
    )

    Second custom column

    ReviewStatus =
IF (
    Features[FeatureStatus] <> "Rejected"
        && Features[FeatureStatus] <> "Rejected - Workaround Available"
        && Features[FeatureStatus] <> "Generally Available"
        && Features[FeatureStatus] <> "Public Preview"
        && Features[FeatureStatus] <> "Private Preview"
        && Features[Semester Planning] <> "No Plan",
    "Not Planned",
    "Planned"
)

    Thanks
    A

8 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi ihartdata 

    Whats the issue?
    Can you elaborate?

    Thanks!
    A

    • ihartdata's avatar
      ihartdata
      Microsoft Employee
      7 years ago
      Hi Anonymous, I can’t figure out how to add bullet 4 to the IF statement because there are 5 options to exclude and a reference from another table...

      I was thinking maybe it was multiple OR statements with &&... but I couldn’t get it to work....
      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        Hey ihartdata 
        I suggest you would provide sample data and expected results.
        It is hard to follow your examples and calculations.
        CheerS!