move dax measure to Power Query Side

[Anonymous]

Hi Experts 

is it possible to move the following measure __S  (as shown below) to the power query side.  i believe this major a offender  as It is a SUMX of ADDCOLUMNS of a SUMX of ADDCOLUMNS which is dependent on an EARLIER conditional.  This particular set of functions are going to result in multiple full table scans in a recursive scenario.  Is there any way that these functions can be either simplified or pre-calculated on the Power Query side

__s =

VAR Mytable1 =

ADDCOLUMNS (

VALUES ( PMS_FINANCIAL_PDS[Month Start] ),

"CountComplaints", CALCULATE ( COUNTROWS ( PMS_COMPLAINT ) ),

"Index", RANKX ( PMS_FINANCIAL_PDS, PMS_FINANCIAL_PDS[Month Start],,, DENSE )

)

RETURN

SUMX (

Mytable1,

SUMX (

Mytable1,

IF (

[Index] > EARLIER ( [Index] )

&& [CountComplaints] < EARLIER ( [CountComplaints] ),

1,

IF (

[Index] > EARLIER ( [Index] )

&& [CountComplaints] > EARLIER ( [CountComplaints] ),

-1

)

)

)

)

[Greg_Deckler]

Hard to say with the information provided, not sure what this is used for or how it is used. Probably easier to make it a DAX column than port it to Power Query. That would solve the report performance problems.

[Anonymous]

Hi greg

this is the measure you tweaked for me some time back (mann kandell) analysis (way back last year october 2019. in total i have 5 measure that link together in order to calculate the p-value..

__s =

VAR Mytable1 =

ADDCOLUMNS (

VALUES ( PMS_FINANCIAL_PDS[Month Start] ),

"CountComplaints", CALCULATE ( COUNTROWS ( PMS_COMPLAINT ) ),

"Index", RANKX ( PMS_FINANCIAL_PDS, PMS_FINANCIAL_PDS[Month Start],,, DENSE )

)

RETURN

SUMX (

Mytable1,

SUMX (

Mytable1,

IF (

[Index] > EARLIER ( [Index] )

&& [CountComplaints] < EARLIER ( [CountComplaints] ),

1,

IF (

[Index] > EARLIER ( [Index] )

&& [CountComplaints] > EARLIER ( [CountComplaints] ),

-1

)

)

)

)

links to

__se = SQRT( ([__n]*([__n]-1)*(2*[__n]+5)-[__freq])/18) (standard error)

links to 

__z-stat = IF(

[__S]>0,([__S] - 1)/[__se],IF(

[__S]<0,([__S] + 1)/[__se],

0

))

and finally the p-value calculation

p-value = 2 * NORM.S.DIST(-ABS([__z-stat]),TRUE)

this report has become super slow in rendering certain visuals.

[Greg_Deckler]

Hmm, I will have to look at what an become columns in all of that. This was during my flurry of fascination with measures. I have since come to realize the wisdom of columns.

[Anonymous]

Thanks Greg