PBI QUERY Taking too long

[rogerrendon]

Hi, so I'm new to PBI. Im using a 250k line db via excel, in which the data is about congress votations. I need to calculate de moving avrg but almost always there are more than one votation per day and many times a votation has several parts, so I think this is making it more dificult. I came before some DAX formulas and test them out I think  I finaly found some thing that makes sense but the query is taking too long, about  700k ms. I think there is a way to optimize the query but Im having trouble knowing how. I use two columns [Data] (DATE) and [Custom] which is a binary count for Yes, No and Other Voting, Yes =1 , No and Other = 0. 

Here is the DAX:

Avg Votes (MA 30 days) =

VAR _LastDate = MAx('2022'[Data])

VAR _Duration = 30

VAR _CalculationPeriod =

FILTER ( ALL('2022'),

AND (

    '2022'[Data] > _LastDate - _Duration,

    '2022'[Data] <= _LastDate

)

)

VAR _MovingAvergae =

IF (

    COUNTROWS(_CalculationPeriod) >= _Duration,

    CALCULATE( AVERAGE('2022'[Custom]), _CalculationPeriod

    )

)

RETURN

_MovingAvergae

Also I used the performance analyzer: 

// DAX Query
DEFINE
VAR __DS0FilterTable =
TREATAS({"56ª Legislatura"}, '2022'[Legislatura])

VAR __DS0FilterTable2 =
TREATAS({"4ª Sessão Legislativa"}, '2022'[Sessão])

VAR __DS0FilterTable3 =
FILTER(
KEEPFILTERS(VALUES('2022'[Data])),
AND(
AND(
AND('2022'[Data] >= DATE(2022, 2, 4), '2022'[Data] < DATE(2023, 2, 4)),
'2022'[Data] >= DATE(2022, 11, 3)
),
'2022'[Data] < DATE(2022, 12, 22)
)
)

VAR __DS0Core =
SUMMARIZECOLUMNS(
'LocalDateTable_bd1749d9-de1f-435c-8a93-6bbfe052facc'[Year],
'LocalDateTable_bd1749d9-de1f-435c-8a93-6bbfe052facc'[Month],
'LocalDateTable_bd1749d9-de1f-435c-8a93-6bbfe052facc'[MonthNo],
'LocalDateTable_bd1749d9-de1f-435c-8a93-6bbfe052facc'[Day],
'2022'[Tema_parte],
__DS0FilterTable,
__DS0FilterTable2,
__DS0FilterTable3,
"AverageCustom", CALCULATE(AVERAGE('2022'[Custom])),
"Avg_Votes_MA_7_days", '2022'[Avg Votes (MA 7 days)]
)

VAR __DS0PrimaryWindowed =
TOPN(
1001,
__DS0Core,
'LocalDateTable_bd1749d9-de1f-435c-8a93-6bbfe052facc'[Year],
1,
'LocalDateTable_bd1749d9-de1f-435c-8a93-6bbfe052facc'[MonthNo],
1,
'LocalDateTable_bd1749d9-de1f-435c-8a93-6bbfe052facc'[Month],
1,
'LocalDateTable_bd1749d9-de1f-435c-8a93-6bbfe052facc'[Day],
1,
'2022'[Tema_parte],
1
)

EVALUATE
__DS0PrimaryWindowed

ORDER BY
'LocalDateTable_bd1749d9-de1f-435c-8a93-6bbfe052facc'[Year],
'LocalDateTable_bd1749d9-de1f-435c-8a93-6bbfe052facc'[MonthNo],
'LocalDateTable_bd1749d9-de1f-435c-8a93-6bbfe052facc'[Month],
'LocalDateTable_bd1749d9-de1f-435c-8a93-6bbfe052facc'[Day],
'2022'[Tema_parte]

[rogerrendon · Accepted solution]

So, just to close this question, aparently it was taking too long due to date hierarchy, I disabled that and leaved only date and that solved my problem.

[amitchandak]

@rogerrendon , check if you can window function for the same

Power BI Window function Rolling, Cumulative/Running Total, WTD, MTD, QTD, YTD, FYTD: https://youtu.be/nxc_IWl-tTc

[rogerrendon]

Hi @amitchandak, so are you saying to substitue the VAR calculationperdiod? Instead of FILTER use WINDOW?