cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rbirch4404 Frequent Visitor
Frequent Visitor

If Condition for Text not Working

Hey, I have a [Status] Column where I'm trying to consolidate the 7 different types into either Open or Closed. I've tried multiple IF statements, but PBI doesn't seem to be reading my data correctly. 

 

For example, the following are the status types...

-(Blank)

-Assigned

-Closed with Feedback

-Pending Expert

-Retired

-Submission Declined

-Work in Progress

 

Retired, Submission Declined, and Closed with Feedback all need to be grouped into status Classification: Closed. All the others need to be grouped: Open. 

 

My intial formula to test just one did not work: IF(Table1[Status] = "Retired", "Closed", Table1[Status]).

The new row just mirrors the old row. So somehow this is a false statement, I just don't know how to fix it. 

 

Any help would be appreciated. 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User IV
Super User IV

Re: If Condition for Text not Working

Such things should be done in Power Query (M), not in DAX. Use the right tool for the job. On top of that, M code would be very simple. No messing around with context and context transition.

DAX is a Data Analysis eXpressions language, not data mash-up language. M is.

Best
D.

View solution in original post

Super User IV
Super User IV

Re: If Condition for Text not Working

Hi there.

Again, this is not the way to do it. First, you should create an auxiliary table that will hold all the mappings from status to classification. Then, in PQ, you left outer join the target table to the mappings table and get the column you need.

This procedure makes sure you don't have to go back to the M code when you need to change a mapping or add a new one. All you have to do is manage the mappings which can be done in the source through SQL (if your data comes from a relational database).

Best
D.

View solution in original post

7 REPLIES 7
buffanut Regular Visitor
Regular Visitor

Re: If Condition for Text not Working

I would try to accomplish this with a measure using a switch statement such as:
status = switch([status],

Retired, "Closed"

Submission Declined, "Closed"

Closed with Feedback, "Closed"

assigned,"Open"

etc...

)
     

buffanut Regular Visitor
Regular Visitor

Re: If Condition for Text not Working

If you wanted to stick with an IF statement it would have to be nested such as:

IF([status] = "retired","Closed",(IF([status] = "Submission Declined","Closed",(IF([status] ="assigned","open",(IF etc.. )))

but that could get long and messy which is why i recomend the switch statement above

Highlighted
rbirch4404 Frequent Visitor
Frequent Visitor

Re: If Condition for Text not Working

So I tested this out for a few of the status...

 

switch(Table1[Status],"Retired", "Closed", "Submission Declined", "Closed", "Assigned", "Open"). 
 
This resulted the entire column going to blank. Did I type this in incorrectly or do I have to fill out all of the 7 status for it to work?
 
Super User IV
Super User IV

Re: If Condition for Text not Working

Such things should be done in Power Query (M), not in DAX. Use the right tool for the job. On top of that, M code would be very simple. No messing around with context and context transition.

DAX is a Data Analysis eXpressions language, not data mash-up language. M is.

Best
D.

View solution in original post

ayushqralgroup Frequent Visitor
Frequent Visitor

Re: If Condition for Text not Working

You can use IF in conjunction with OR logical operator as shown below:

 

Create a new column:

Final Status = IF( Table1[Status] = "Retired" ||Table1[Status] ="Submission Declined" || Table1[Status] ="Closed with Feedback", "Closed","Open") @rbirch4404 

 

Hope this helps!

rbirch4404 Frequent Visitor
Frequent Visitor

Re: If Condition for Text not Working


@darlove wrote:
Such things should be done in Power Query (M), not in DAX. Use the right tool for the job. On top of that, M code would be very simple. No messing around with context and context transition.

DAX is a Data Analysis eXpressions language, not data mash-up language. M is.

Best
D.

@darlove Thanks for pointing this out. I was not aware of the two langauges. 

I was able to use the Replace Value function on the Column inside the Advanced Editor and now it works. 

 

#"Replaced Value" = Table.ReplaceValue(#"Duplicated Column","Work In Progress","Open",Replacer.ReplaceText,{"Status - Copy"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","Pending Expert","Open",Replacer.ReplaceText,{"Status - Copy"}),
#"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1","Assigned","Open",Replacer.ReplaceText,{"Status - Copy"}),
#"Replaced Value3" = Table.ReplaceValue(#"Replaced Value2","Pending Submitter Information","Open",Replacer.ReplaceText,{"Status - Copy"}), ..... etc

Super User IV
Super User IV

Re: If Condition for Text not Working

Hi there.

Again, this is not the way to do it. First, you should create an auxiliary table that will hold all the mappings from status to classification. Then, in PQ, you left outer join the target table to the mappings table and get the column you need.

This procedure makes sure you don't have to go back to the M code when you need to change a mapping or add a new one. All you have to do is manage the mappings which can be done in the source through SQL (if your data comes from a relational database).

Best
D.

View solution in original post

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors
Top Kudoed Authors