convert Crystal Formula to DAX or Power Query

[shenderson2]

Hi team, 

coming from lots of years in crystal reports to now moving to Power BI and DAX or Powerquery.

This formula works great in Crystal, how can i now convert it to work in Power BI? 

IF (Mid ({CASE_CHARGE.OFFENSE_CODE}, 8,1 ) = 'F' or  {CASE_CHARGE.GRADE} in ['A', ‘B’, ‘C’, ‘F’, ‘T’] then “Felony” else

if (Mid ({CASE_CHARGE.OFFENSE_CODE}, 8,1 ) = 'M' or {CASE_CHARGE.GRADE} = 'M')  then "Misdemeanor" else

if (Mid ({CASE_CHARGE.OFFENSE_CODE}, 8,1 ) = 'O' or {CASE_CHARGE.GRADE} = 'O') then "Ordinance" else

“UNKNOWN”

THANKS

[v-henryk-mstf]

Hi @shenderson2 ,

There does not seem to be an error in the logic of the formula, according to the error hint indicates that it is the field referenced in the formula that is in error.

Would you be able to provide information on screenshots of the data model, etc.? I will answer you as soon as possible.

Best Regards,
Henry

[tackytechtom]

Hi @shenderson2 ,

I am not a crack when it comes to crystal report syntax, but I would translate it to this (in DAX, calculated column)

Calculated Column =
SWITCH (
    TRUE,
    MID ( 'CASE_CHARGE'[OFFENSE_CODE], 8, 1) = "F" || 'CASE_CHARGE'[GRADE] in {"A", "B", "C", "F", "T"}, "Felony",
    MID ( 'CASE_CHARGE'[OFFENSE_CODE], 8, 1) = "M" || 'CASE_CHARGE'[GRADE] = "M", "Misdemeanor",
    MID ( 'CASE_CHARGE'[OFFENSE_CODE], 8, 1) = "O" || 'CASE_CHARGE'[GRADE] = "O", "Ordinance",
    "UNKNOWN"
)

Let me know, if this works! 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

[shenderson2]

HI Tom, 

i am very sorry to tell but unfortunately it did not work. 

Have a lovely weekend

[tackytechtom]

Hi @shenderson2 ,

It does not look too bad, I would say 🙂

Unfortunately, I cannot see the error message, but could it be that you tried to create a measure instead of a calculated column? 

Otherwise, feel free to share some data and I am sure we will be getting there!

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

[shenderson2]

Yes, i did try to create a  measure.  It is a direct query without any data transformations so far. I tried to create a new column in the query editor using your code without success.