IF OR AND expression correction

[cristianml]

Hi,

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

Thanks!

[lbendlin · Accepted solution]

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.

[Sunil2020 · Accepted solution]

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 · Accepted solution]

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.

[cristianml]

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 measure

After measure:

Thanks.!

[cristianml]

Hi @lbendlin,

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

Why happend this ? How can be solved?

Thanks

[lbendlin]

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.