Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet 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
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:
// 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]
Solved! Go to 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.
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.
@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
Hi @amitchandak, so are you saying to substitue the VAR calculationperdiod? Instead of FILTER use WINDOW?
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
90 | |
87 | |
84 | |
68 | |
49 |
User | Count |
---|---|
131 | |
111 | |
96 | |
71 | |
67 |