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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
rogerrendon
New Member

PBI QUERY Taking too long

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]

1 ACCEPTED SOLUTION
rogerrendon
New Member

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.

View solution in original post

3 REPLIES 3
rogerrendon
New Member

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

@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

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

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

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.