cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
cristianml
Post Patron
Post Patron

IF OR AND expression correction

Hi,

Could you help me to correct the following expression according to the table below? I don't see where is the the mistake:

formula error.jpg

Thanks!

2 ACCEPTED SOLUTIONS
lbendlin
Super User III
Super User III

switch(true

,[Var CCI]<=-100000 || [Var CCI %]<=-.01,"High"

,[Var CCI]<=-50000 && [Var CCI %]<=-.01,"Medium"

,[Var CCI]>=-50000 && [Var CCI %]<=0,"Low"

,"Green")

 

 

I don't think you can use "%"  in your comparisons.

View solution in original post

Sunil2020
Frequent Visitor

Hi

I'm also new to Power BI. But observed something incorrect with the logic of the requirement you posted - there seems to be 2 anomalies in it. 

1) Unless you move the Medium risk portion of the formula above the High Risk portion, Medium risk will never be tagged to any record as you have "VAR CCI %" <= -1% with the OR clause in the High risk definition while this is part of Medium risk definition with an And Clause. 

2) Var CCI = -50K (equal to 50K) is part of both Medium as well as low risk definition. You need to exclude it from one of them.

 

Regards

Sunil

View solution in original post

5 REPLIES 5
Sunil2020
Frequent Visitor

Hi

I'm also new to Power BI. But observed something incorrect with the logic of the requirement you posted - there seems to be 2 anomalies in it. 

1) Unless you move the Medium risk portion of the formula above the High Risk portion, Medium risk will never be tagged to any record as you have "VAR CCI %" <= -1% with the OR clause in the High risk definition while this is part of Medium risk definition with an And Clause. 

2) Var CCI = -50K (equal to 50K) is part of both Medium as well as low risk definition. You need to exclude it from one of them.

 

Regards

Sunil

View solution in original post

lbendlin
Super User III
Super User III

switch(true

,[Var CCI]<=-100000 || [Var CCI %]<=-.01,"High"

,[Var CCI]<=-50000 && [Var CCI %]<=-.01,"Medium"

,[Var CCI]>=-50000 && [Var CCI %]<=0,"Low"

,"Green")

 

 

I don't think you can use "%"  in your comparisons.

View solution in original post

Hi @lbendlin ,

 

Thanks for the correction but it seems that when I drag this measure into the pivot table the number of lines are multiplied as if calculating with the other lines. I have 35 lines only but when drag the measure I see 1260 lines ! Why happend this ? is there some way to avoid this?

 

Before measure: 

 

 

Before measureBefore measure

 

After measure:

not ok.png

Thanks.!

Hi @lbendlin,

I dont have empty values in my table, that happens when I drag the measure into the pivot table: See below:

Media1.gif

 

Why happend this ? How can be solved?

Thanks

You seem to have lots of rows where K and L columns are empty. According to your formula that translates to "Low" .  You can add a check against empty earlier in the SWITCH() statement.

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors