Convert Crystal Report Formula to use in Power BI query

[pankajj]

Dear community,
I have an existing report done with Crystal Report.  While converting it to Power BI, I am stuck at the following Formula:

local stringvar warehouse := "";

if {Command.ABD_ORDER_POINT} - {Command.ABD_QIS} + {Command.ABD_QID} > {Command.ABDNetQty} then
(IF warehouse = "" then warehouse := warehouse + "*ABD*" else warehouse := warehouse + ", *ABD*")
else if {Command.ABD_ORDER_POINT} > 0 then
(if warehouse = "" then warehouse := warehouse + "ABD" else warehouse := warehouse + ", ABD");

if {Command.ALS_ORDER_POINT} - {Command.ALS_QIS} + {Command.ALS_QID} > {Command.ALSNetQty} then
(IF warehouse = "" then warehouse := warehouse + "*ALS*" else warehouse := warehouse + ", *ALS*")
else if {Command.ALS_ORDER_POINT} > 0 then
(IF warehouse = "" then warehouse := warehouse + "ALS" else warehouse := warehouse + ", ALS");

if {Command.ALSVC_ORDER_POINT} - {Command.ALSVC_QIS} + {Command.ALSVC_QID} > {Command.ALSVCNetQty} then
(IF warehouse = "" then warehouse := warehouse + "*ALS-VC*" else warehouse := warehouse + ", *ALS-VC*")
else if {Command.ALSVC_ORDER_POINT} > 0 then
(IF warehouse = "" then warehouse := warehouse + "ALS-VC" else warehouse := warehouse + ", ALS-VC");

if isnull({Command.NEXT_ORDER_REQUIRED_BY}) = false then
(IF warehouse = "" then warehouse := warehouse + "*" + (IF {Command.DEMAND_WHSE} = "UNIVERSAL" THEN "C2" ELSE {Command.DEMAND_WHSE}) + "*" else warehouse := warehouse + ", *" + (IF {Command.DEMAND_WHSE} = "UNIVERSAL" THEN "C2" ELSE {Command.DEMAND_WHSE}) + "*");

warehouse

We have so many great talent in the community, looking forward for a quick help.

Thank you very much in advance.

[dax]

Hi pankajj,

I am not professional in crystal report, in your scenario, it looks like the conditional column, so if possible, could you please inform me more detailed information(such as your sample data and your expected output)? Then I will help you more correctly.

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[pankajj]

Hi Zoe,

Thanks so much for your response. and sorry for the late reply. I did not receive any email alert on your response.

The Crystal query defines the 'Warehouse' based on various conditions. I was able to convert the query to run in Power BI as below however Power BI is not able to recognize 'warehouse'

if ([ABD_ORDER_POINT] - [ABD_QIS] + [ABD_QID]) > [ABDNetQty] then
if warehouse = "" then warehouse & "*ABD*" else warehouse & " *ABD*"
else if [ABD_ORDER_POINT] > 0 then
if warehouse = "" then warehouse & "ABD" else warehouse & ", ABD" else

if ([ALS_ORDER_POINT] - [ALS_QIS] + [ALS_QID]) > [ALSNetQty] then
if warehouse = "" then warehouse & "*ALS*" else warehouse & ", *ALS*"
else if [ALS_ORDER_POINT] > 0 then
if warehouse = "" then warehouse & "ALS" else warehouse & ", ALS" else

if ([ALSVC_ORDER_POINT] - [ALSVC_QIS] + [ALSVC_QID]) > [ALSVCNetQty] then
if warehouse = "" then warehouse & "*ALS-VC*" else warehouse & ", *ALS-VC*"
else if [ALSVC_ORDER_POINT] > 0 then
if warehouse = "" then warehouse & "ALS-VC" else warehouse & ", ALS-VC" else

if [NEXT_ORDER_REQUIRED_BY] <> null then
if warehouse = "" then warehouse & "*" & (if [DEMAND_WHSE] = "UNIVERSAL" then "C2" else [DEMAND_WHSE]) & "*" else warehouse & ", *" & (if [DEMAND_WHSE] = "UNIVERSAL" then "C2" else [DEMAND_WHSE]) & "*"
else
warehouse

Can you help.

Thank you very much for your support.

Best regards,

Pankajj