Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
cristianml
Post Prodigy
Post Prodigy

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
Super User

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

lbendlin
Super User
Super User

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.

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors