Forum Discussion
Resolver IIIDAX Calculated Table Summarize Returns Granular Results
Hi all,
Using PBID 2.135 (Aug, 2024)
I have a Calc Table I am testing out to move to virtual tables in a measure.
In DAX below I SelectColumns in the first table and then Summarize in a n second table But the second table is reurning more than 200K rows. I expect 109 rows which is what the 3rd table is giving me.
Why would the 2nd table return so many rows?
Thanks,
-w
Test =
VAR __vTable10 =
SELECTCOLUMNS(
Data,
"SummarizedDate", Data[Date],
"Total Revenue",[KPI Selected Revenue Value]
)
VAR ___vTable20 =
SUMMARIZE(
__vTable10,
[SummarizedDate],
[Total Revenue]
)
VAR x =
ADDCOLUMNS(
SUMMARIZE(
Data,
Data[Date]
),
"Total Revenue", [KPI Selected Revenue Value]
)
Return x
Try using FILTER instead of DATESINPERIOD. I tested this formula with 2 days moving average. You will need to use EDATES to get the date x months ago then add one to it to get the start date which should be the date after x months ago.
Thanks danextian ,
I added a my max and min dates as I only need to calculate moving avg for the most recent 15 months. Changed to a 6 M MA. This is now returning exactly what I was looking for. Thanks for your good help
-wtstTable Moving Average =
Var __MaxDate = [KPI Max Date]
VAR __vTable10 =
SELECTCOLUMNS(
Data,
"SummarizedDate", Data[Date],
"Total Revenue", [KPI Selected Revenue Value]
)
VAR __vTable20 =
GROUPBY(
__vTable10,
[SummarizedDate],
"@Total Revenue", SUMX( CURRENTGROUP(), [Total Revenue] )
)
VAR __vTable25 =
FILTER(
__vTable20,
[SummarizedDate] >= [Selected Min Date] &&
[SummarizedDate] <= __MaxDate
)
VAR __vTable30 =
ADDCOLUMNS(
__vTable25,
"6M Moving Avg",
VAR CurrentDate = [SummarizedDate]
RETURN
AVERAGEX(
FILTER(
__vTable20,
[SummarizedDate] >= EDATE(CurrentDate,-5) &&
[SummarizedDate] <= CurrentDate
),
[@Total Revenue]
)
)
RETURN
__vTable30
7 Replies
- danextian
Super User
Hi tecumseh
SELECTCOLUMNS creates a table of selected columns from a table and/or from an expression thus in the second variable, you're simply selecting the columns and not actually doing an aggregation. The second variable could be written asVAR ___vTable20 = GROUPBY( __vTable10, [SummarizedDate], "Total Revenue", SUMX(CURRENTGROUP(), [Total Revenue]) )That aside, why the need to use the first two variables of just doing x instead?
- rajendraongole1
Hi tecumseh -I think your total Revenue is properly aggregated over the SummarizedDate field, and it should return the correct number of rows (109 in your case).
below changes works in your dax, please check
VAR ___vTable20 =
SUMMARIZE(
__vTable10,
[SummarizedDate],
"Total Revenue", SUMX(__vTable10, [Total Revenue])
)Hope this helps.
tecumsehThanks danextian ,
That did the job.
Part of a solution for Moving Average.
Tried chatting with ChatGPT about it tied many suggestions - alway get an error
I think the error is down to 1 date in DatesInPeriod
ChatGPT last suggestion (I hit max) was to use SelectColumns on that SummarizedDate but that returned 500K rows when I expect to start with 109. In the end I need 15.
This is current Moving Average bit but still showing an error on [SummarizedData]
Thanks,
-wVAR __vTable3 =
ADDCOLUMNS(
__vTable2,
"3M Moving Average",
CALCULATE(
AVERAGEX(
DATESINPERIOD(
Bookings[Date],
[SummarizedDate], -- showing error here
-3,
MONTH
),
[KPI Selected Revenue Value]
)
)
)- danextian
Hi tecumseh
Assign SummarizedDate virtual column to a variable so you can access it within the context of DATESINPERIOD.
This works for me but I haven't tested the correctness of the result.
test = VAR __vTable10 = SELECTCOLUMNS ( Data, "SummarizedDate", Data[Date], "Total Revenue", [Total Revenue] ) VAR __vTable20 = GROUPBY ( __vTable10, [SummarizedDate], "@Total Revenue", SUMX ( CURRENTGROUP (), [Total Revenue] ) ) VAR __vTable30 = ADDCOLUMNS ( __vTable20, "3M Moving Average", VAR CurrentDate = [SummarizedDate] VAR TotalRev = [@Total Revenue] RETURN CALCULATE ( AVERAGEX ( DATESINPERIOD ( Dates[Date], CurrentDate, -3, MONTH ), TotalRev ) ) ) RETURN __vTable30
