cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ihartdata
Microsoft
Microsoft

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. InPreviewFeature Status IS "Private Preview" OR "Public Preview"
  3. RejectedFeature 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. 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

@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

View solution in original post

8 REPLIES 8
Anonymous
Not applicable

Hi @ihartdata 

Whats the issue?
Can you elaborate?

Thanks!
A

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
Not applicable

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

Yes, I can see how that would be helpful. 🙂  Here you go: 

https://1drv.ms/u/s!AvNGvMlvmRwehaZW31CENJojdL6C1w?e=pWMYcd 

 

Let me know what other questions you have and thank  you for looking @Anonymous 

Anonymous
Not applicable

@ihartdata 
Again,
I am having trouble understanding your syntax.

Once you clarify what you need, we might assist.
CheerS!
A

Hi, 

 

Sorry, let me simplify my inquiry.

 

using the example pbix file, I'm trying to create a calculated column.  Where IF the column FeatureStatus in the Features table is anything other than Rejected, Rejected – Workaround Available, Generally Available, Public Preview, Private Preview AND the column Semester Planning in the Release Lookup table IS "No Plan" then it should be Not Planned, otherwise Planned. 

 

Does that make sense @Anonymous ? 

@ihartdata 

 

Hi,

 

Please see the pbix file here.

 

Hope this should solve the purpose.

 

Thanks,

Vivek

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous
Not applicable

@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

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.