Forum Discussion

Bansi008's avatar
Bansi008
Helper III
4 years ago

Need help on Power BI Query.

Hi team, I am currently working on my borganizational project and i am trying to create new measure for my database using below query. However i can't able to add new measure due to syntax error. Can someone please help me.

 

Risk Level = var Impact = AVERAGE(Output[RiskImpact])
var Likelihood = AVERAGE(Output[RiskLikelihood])
return
SWITCH(TRUE() ,
Impact > 0 && Impact <= 2 && Likelihood > 0 && Likelihood <=4 , "LOW" ,
Impact > 0 && Impact <= 2 && Likelihood > 4 && Likelihood <=5 , "MEDIUM" ,
Impact > 2 && Impact <= 3 && Likelihood > 0 && Likelihood <=4 , "MEDIUM" ,
Impact > 2 && Impact <= 3 && Likelihood > 4 && Likelihood <=5 , "HIGH" ,
Impact > 3 && Impact <= 4 && Likelihood > 0 && Likelihood <=3 , "MEDIUM" ,
Impact > 3 && Impact <= 4 && Likelihood > 3 && Likelihood <=5 , "HIGH" ,
Impact > 4 && Impact <= 5 && Likelihood > 0 && Likelihood <=1 , "MEDIUM" ,
Impact > 4 && Impact <= 5 && Likelihood > 1 && Likelihood <=5 , "HIGH" ,
"NA” )
Need Help

2 Replies

  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    Most Valuable Professional
    4 years ago

    Problem is about second quote in the formula for NA. In place of "NA” use "NA"

    Hence following formula would work

     

    Risk Level = 
var Impact = AVERAGE(Output[RiskImpact])
var Likelihood = AVERAGE(Output[RiskLikelihood])
return
SWITCH(TRUE() ,
Impact > 0 && Impact <= 2 && Likelihood > 0 && Likelihood <=4 , "LOW" ,
Impact > 0 && Impact <= 2 && Likelihood > 4 && Likelihood <=5 , "MEDIUM" ,
Impact > 2 && Impact <= 3 && Likelihood > 0 && Likelihood <=4 , "MEDIUM" ,
Impact > 2 && Impact <= 3 && Likelihood > 4 && Likelihood <=5 , "HIGH" ,
Impact > 3 && Impact <= 4 && Likelihood > 0 && Likelihood <=3 , "MEDIUM" ,
Impact > 3 && Impact <= 4 && Likelihood > 3 && Likelihood <=5 , "HIGH" ,
Impact > 4 && Impact <= 5 && Likelihood > 0 && Likelihood <=1 , "MEDIUM" ,
Impact > 4 && Impact <= 5 && Likelihood > 1 && Likelihood <=5 , "HIGH",
"NA")