Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
shenderson2
New Member

convert Crystal Formula to DAX or Power Query

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

 

5 REPLIES 5
v-henryk-mstf
Community Support
Community Support

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
Super User
Super User

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/



Did I answer your question➡️ Please, mark my post as a solution ✔️

Also happily accepting Kudos 🙂

Feel free to connect with me on LinkedIn! linkedIn

#proudtobeasuperuser 

dax error.JPGHI Tom, 

 

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

Have a lovely weekend

 

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/



Did I answer your question➡️ Please, mark my post as a solution ✔️

Also happily accepting Kudos 🙂

Feel free to connect with me on LinkedIn! linkedIn

#proudtobeasuperuser 

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.