DAX to M Code

[itsmeanuj]

Hi Guys,

My PBI Workbook has become very heavy lately and refreshing takes 20GB of memory. my system is 32 GB and I am afraid that it will surpass that memory and would need more space. there are few columns which I have calculated in DAX and I believe they are taking most of the memory while refreshing. I believe that if I shift those calculations from DAX to M code then it will take less memory. Can someone please help me in converting the below DAX formula to M code? i will take it as a reference and will convert the remaining DAX calculated columns to M code.

(TTFF) Patient TTFF Interim Calculations (Net Days) =

VAR First_Ship_Date_COMM = CALCULATE(MIN('Prometrics Transaction Data (Shipment)'[Status Date Sort]),'Prometrics Transaction Data (Shipment)'[STATUS_CODE] = "Active", 'Prometrics Transaction Data (Shipment)'[SUBSTATUS_CODE]= "Shipment",NOT('Prometrics Transaction Data (Shipment)'[PHARM_CODE] IN {"HUB"}),ALLEXCEPT('Prometrics Transaction Data (Shipment)','Prometrics Transaction Data (Shipment)'[(TTFF) Pharm Patient ID]))

VAR First_Referral_Date_COMM = CALCULATE(MIN('Prometrics Transaction Data (Shipment)'[REF_DATE]),NOT('Prometrics Transaction Data (Shipment)'[PHARM_CODE] IN {"HUB"}),ALLEXCEPT('Prometrics Transaction Data (Shipment)','Prometrics Transaction Data (Shipment)'[(TTFF) Pharm Patient ID]))

var last_cancelled_prior_to_first_ship = CALCULATE(MAX('Prometrics Transaction Data (Shipment)'[Status Date Sort]),'Prometrics Transaction Data (Shipment)'[STATUS_CODE]="CANCELLED",NOT('Prometrics Transaction Data (Shipment)'[PHARM_CODE] IN {"HUB"}),'Prometrics Transaction Data (Shipment)'[Status Date Sort]<First_Ship_Date_COMM,ALLEXCEPT('Prometrics Transaction Data (Shipment)','Prometrics Transaction Data (Shipment)'[(TTFF) Pharm Patient ID]))

var first_pending_new_post_last_cancelled = IF(last_cancelled_prior_to_first_ship=BLANK(),BLANK(),CALCULATE(MIN('Prometrics Transaction Data (Shipment)'[Status Date Sort]),'Prometrics Transaction Data (Shipment)'[STATUS_CODE] = "PENDING", /*'Prometrics Transaction Data (Shipment)'[SUBSTATUS_CODE]= "NEW",*/NOT('Prometrics Transaction Data (Shipment)'[PHARM_CODE] IN {"HUB"}),'Prometrics Transaction Data (Shipment)'[Status Date Sort]>=last_cancelled_prior_to_first_ship,ALLEXCEPT('Prometrics Transaction Data (Shipment)','Prometrics Transaction Data (Shipment)'[(TTFF) Pharm Patient ID])))

var first_date_selector = IF(first_pending_new_post_last_cancelled<>BLANK(),first_pending_new_post_last_cancelled,First_Referral_Date_COMM)

RETURN
IF(
'Prometrics Transaction Data (Shipment)'[STATUS_CODE] = "Active"&&'Prometrics Transaction Data (Shipment)'
[SUBSTATUS_CODE]= "Shipment"&&NOT('Prometrics Transaction Data (Shipment)'[PHARM_CODE] IN {"HUB"})
&&'Prometrics Transaction Data (Shipment)'[Status Date Sort] = First_Ship_Date_COMM,
IF(
OR(ISBLANK(First_Ship_Date_COMM),ISBLANK(first_date_selector)),BLANK(),
IF(
OR(LEFT('Prometrics Transaction Data (Shipment)'[(TTFF) Pharm Patient ID],3)="BIO",
LEFT('Prometrics Transaction Data (Shipment)'[(TTFF) Pharm Patient ID],3)="OCO"),
CALCULATE(SUM(Date_Ref[(Date) Business Day Flag]),DATESBETWEEN
(Date_Ref[Date],first_date_selector,First_Ship_Date_COMM))-1 -
CALCULATE(SUM('Prometrics Transaction Data (Shipment)'[(TTFF) Hold Time]),
ALLEXCEPT('Prometrics Transaction Data (Shipment)',
'Prometrics Transaction Data (Shipment)'[(TTFF) Pharm Patient ID])),
IF(
(CALCULATE(SUM(Date_Ref[(Date) Business Day Flag]),DATESBETWEEN(Date_Ref[Date],
first_date_selector,First_Ship_Date_COMM))-1)=-1,0,
IF(
OR(WEEKDAY(First_Ship_Date_COMM)=1,WEEKDAY(First_Ship_Date_COMM)=7),
CALCULATE(SUM(Date_Ref[(Date) Business Day Flag]),DATESBETWEEN(Date_Ref[Date],
first_date_selector,First_Ship_Date_COMM)),
CALCULATE(SUM(Date_Ref[(Date) Business Day Flag]),DATESBETWEEN(Date_Ref[Date],
first_date_selector,First_Ship_Date_COMM))-1
)
)
)
),BLANK()
)

[Anonymous]

HI @itsmeanuj,

In fact, the M query is good at the shape and transform table structure and they are not suitable to handle calculations that loop through the whole table. They will spend more resources and may cause a 'memory leak' due to the looping calculations.

For your scenario, they should be related to your DAX formula which nested multiple batch variable loop through tables and spends multiple resource usage due to the looping. Also, the heavy data amount obviously increases the workload of the calculations. 

For your calculated column expression, it includes:

four variables need to calculate through the whole table, two calculates on the table within the if statement, nest if statement with calculation result as condition, and redirects a calculation on table records;
The total calculation amount should be (4+2) * 'table rows' + 'table rows' * 'table row' = 6 * 150k +150K * 150k = 22.5b( 22,500,900,000)

Notice:
The nested looping will obviously multip the calculated amount. 
The real calculated amount may not be similar to the supposed, they can be reduced by filters and resource reuse.

Perhaps you can take a look at the following blog to know how to optimize the nested iterators if help:

Optimizing nested iterators in DAX - SQLBI

Regards,

Xiaoxin Sheng

[Anonymous]

You will need to end up doing a bunch of transformations in Power Query nevertheless, even there are some things that you could only do with DAX. In my experience, I don't think it will make a difference if you switch them to M, it might even take more time. It seems the issue relies on the size of the dataset, the steps applied on Power Query or even the connectors to your data source. 

[itsmeanuj]

ok! you mean to say that memory usage issue would not be solved even if we move some calculations to query editor from DAX?

[Anonymous]

How many rows do you have in your dataset?

[itsmeanuj]

as of now 150K. but it keeps increasing with each passing day.