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

Help on a query. I have an error but I am not sure where. I need to filter on different columns

if [BUSINESS TYPE] <> "New Product" or [BUSINESS TYPE] <> "Distributed Capacity" then 0 else

 

if [BUSINESS TYPE] = "New Product" or [BUSINESS TYPE] = "Distributed Capacity" then

 

if [NO OF MONTHS] < 24 then 0 else

if [NO OF MONTHS] >= 24 and [NO OF MONTHS] <= 36 then
(( [NO OF MONTHS] - 12) / 12) * (.50 * [Tier 1 Rate]) * [FINAL COMMISSIONABLE VALUE USD] else

 

if [NO OF MONTHS] >= 36 then
((36 - 12) / 12) * (.50 * [Tier 1 Rate]) * [FINAL COMMISSIONABLE VALUE USD] else

 

0

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Help on a query. I have an error but I am not sure where. I need to filter on different columns

Hi @Mjwhite1,

 

 

Based on my assumption, you may want a if condition like below:

if [BUSINESS TYPE]<>"New Product" and [BUSINESS TYPE]<> "Distributed Capacity"
then 0

else if [NO OF MONTHS]<24

then 0

else if [NO OF MONTHS]>=24 and [NO OF MONTHS]<=36
then (( [NO OF MONTHS] - 12) / 12) * (.50 * [Tier 1 Rate]) * [FINAL COMMISSIONABLE VALUE USD]
else ((36 - 12) / 12) * (.50 * [Tier 1 Rate]) * [FINAL COMMISSIONABLE VALUE USD]

 

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 REPLIES 4
Super User
Super User

Re: Help on a query. I have an error but I am not sure where. I need to filter on different columns

What error are you actually getting?

 

It'd be much easier to read if you didn't have so much redundant code - it can easily look something like this (clearly needing to get brackets correct):

 

cleaner if statement.PNG

Mjwhite1 Frequent Visitor
Frequent Visitor

Re: Help on a query. I have an error but I am not sure where. I need to filter on different columns

if [BUSINESS TYPE] <> "New Product" or [BUSINESS TYPE] <> "Distributed Capacity" then 0 else

 

if [NO OF MONTHS] < 24 then 0 else

 

[NO OF MONTHS] <= 36 then

 

(( [NO OF MONTHS] - 12) / 12) * (.50 * [Tier 1 Rate]) * [FINAL COMMISSIONABLE VALUE USD] else

((36 - 12) / 12) * (.50 * [Tier 1 Rate]) * [FINAL COMMISSIONABLE VALUE USD] else

 

0

 

I have an EOF error on the : then after the 36. I am new to power BI i have worked with it for a few weeks. It is dealing with two seperate columns. 

Community Support Team
Community Support Team

Re: Help on a query. I have an error but I am not sure where. I need to filter on different columns

Hi @Mjwhite1,

 

 

Based on my assumption, you may want a if condition like below:

if [BUSINESS TYPE]<>"New Product" and [BUSINESS TYPE]<> "Distributed Capacity"
then 0

else if [NO OF MONTHS]<24

then 0

else if [NO OF MONTHS]>=24 and [NO OF MONTHS]<=36
then (( [NO OF MONTHS] - 12) / 12) * (.50 * [Tier 1 Rate]) * [FINAL COMMISSIONABLE VALUE USD]
else ((36 - 12) / 12) * (.50 * [Tier 1 Rate]) * [FINAL COMMISSIONABLE VALUE USD]

 

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Mjwhite1 Frequent Visitor
Frequent Visitor

Re: Help on a query. I have an error but I am not sure where. I need to filter on different columns

Thank you that worked perfectly! Appreciate the help.