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

[Mjwhite1]

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

[v-yulgu-msft · Accepted solution]

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

[Mjwhite1]

Thank you that worked perfectly! Appreciate the help.

[jthomson]

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):

[Mjwhite1]

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.