If function cannot check inside a column

[mokhawaja]

Hi,

pretty exicted for the first post in Power BI forums

i am trying to create a measure based on an if function which should check inside a column called "Class" which has 3 values (A, B, C), then return the value form a measure A if the class is A and return the value from Measure B if the Class is B... so what i am trying to write as a function is the following:

If(Classes[Class] = "A", [Coverage A KPI], If(Classes[Class] = "B", [Coverage B KPI], 0))

so my problem is the if function cannot see the column Class to check inside it... while i could do it easily on Excel but it does not work in Power BI

so please advise how to work it out.

Regards,

Mohammad Alkhawaja

[vanessafvg]

@mokhawaja

Value =
SWITCH (
    Classes[Class],
    Classes[Class] = "A", [Coverage A KPI],
    Classes[Class] = "B", [Coverage B KPI],
    Classes[Class] = "C", [Coverage C KPI],

    0
)

[mokhawaja]

thank you @vanessafvg,

really appreciate your help... i used your suggested formula and it showed me the below error:

kindly advise,

Regards

Mohammad

[vanessafvg]

@mokhawajai think you have to create a new column not a new measure.

[mokhawaja]

Hi,

i tried the column... and now it says:

"Function 'SWITCH' does not support comparing values of type Text with values of type True/False. Consider using the VALUE or FORMAT function to convert one of the values."

[vanessafvg]

try this ?

@mokhawaja

Value =
SWITCH (true(),
    Classes[Class] = "A", [Coverage A KPI],
    Classes[Class] = "B", [Coverage B KPI],
    Classes[Class] = "C", [Coverage C KPI],

    0
)

[mokhawaja · Accepted solution]

the error is gone but it does not give me the right results (it gives me all zeros!)

i re-tried the if function using measure inside the Classes table but also had to wrap it into a MAX function: and now it works perfectly 

Measure =
IF (
    MAX ( Classes[Class] ) = "A", [Coverage A KPI],
    IF ( MAX ( Classes[Class] ) = "B", [Coverage B KPI],
    0 )
)

really appreciate your time and help

Best Regards,

Mohammad

[vanessafvg]

@mokhawaja  well if it doesn't give you the correct result and it returns 0, something is wrong with the conditional statement, wouldn't it be better to try and resolve that?

[KHorseman]

MAX operates on numbers, not text. For text you could use FIRSTNONBLANK or LASTNONBLANK.

[mokhawaja]

Hi..