Join us at FabCon Atlanta from March 16 - 20, 2026, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.
Register now!To celebrate FabCon Vienna, we are offering 50% off select exams. Ends October 3rd. Request your discount now.
Using summarize is not best practice. Best practices using SUMMARIZE and ADDCOLUMNS
The code below works both in daxstudio and in my dataset. The child table MBPL gets a new column added call CheckPoint Next.
Great. But reading the above article using summarize is not good. So how to refactor the below?
--CheckPoint Next = -- uncomment for pbi desktop
EVALUATE -- comment out for pbi desktop but needed for daxstudio
VAR FINANCIALYEARNEXT = SELECTEDVALUE ( 'x Reference Dates'[FINANCIALYEARNEXT] )
VAR MBM_Id = "148" -- testing
--VAR MBM_Id = 'Measure Benefit Master'[Id] -- uncomment for pbi desktop
VAR CHECKPOINTNEXT =
SUMMARIZE (
FILTER (
VALUES ( 'Measure Benefit Progress List' ),
VALUE ( 'Measure Benefit Progress List'[Financial Year Search] ) = VALUE ( FINANCIALYEARNEXT ) &&
'Measure Benefit Progress List'[Main ID LkUpId] = MBM_Id
),
[CheckPoint]
) )
RETURN
{ CHECKPOINTNEXT }
Solved! Go to Solution.
(just a syntax error in your snipette of code, a ")" is redundant before RETURN)
EVALUATE
-- comment out for pbi desktop but needed for daxstudio
VAR FINANCIALYEARNEXT =
SELECTEDVALUE( 'x Reference Dates'[FINANCIALYEARNEXT] )
VAR MBM_Id = "148" -- testing
--VAR MBM_Id = 'Measure Benefit Master'[Id] -- uncomment for pbi desktop
VAR CHECKPOINTNEXT =
SUMMARIZE(
FILTER(
VALUES( 'Measure Benefit Progress List' ),
VALUE( 'Measure Benefit Progress List'[Financial Year Search] )
= VALUE( FINANCIALYEARNEXT )
&& 'Measure Benefit Progress List'[Main ID LkUpId] = MBM_Id
),
[CheckPoint]
)
RETURN
{ CHECKPOINTNEXT }
Nothing to change in your case since you did nothing but just extract distinct values of 'Measure Benefit Progress List'[CheckPoint].
From syntactic perspective, it equals to
VAR CHECKPOINTNEXT =
CALCULATETABLE(
VALUES( 'Measure Benefit Progress List'[CheckPoint] ),
VALUE( 'Measure Benefit Progress List'[Financial Year Search] )
= VALUE( FINANCIALYEARNEXT ),
'Measure Benefit Progress List'[Main ID LkUpId] = MBM_Id
)
Expertise = List.Accumulate( {Days as from Today}, {Skills and Knowledge}, (Current, Everyday) => Current & Day.LearnAndPractise(Everyday) ) |
VAR CHECKPOINTNEXT =
CALCULATETABLE(
VALUES( 'Measure Benefit Progress List'[CheckPoint] ),
VALUE( 'Measure Benefit Progress List'[Financial Year Search] )
= VALUE( FINANCIALYEARNEXT ) &&
'Measure Benefit Progress List'[Main ID LkUpId] = MBM_Id
)
Keeping as one filter by using '&&' gives better timing results.
(just a syntax error in your snipette of code, a ")" is redundant before RETURN)
EVALUATE
-- comment out for pbi desktop but needed for daxstudio
VAR FINANCIALYEARNEXT =
SELECTEDVALUE( 'x Reference Dates'[FINANCIALYEARNEXT] )
VAR MBM_Id = "148" -- testing
--VAR MBM_Id = 'Measure Benefit Master'[Id] -- uncomment for pbi desktop
VAR CHECKPOINTNEXT =
SUMMARIZE(
FILTER(
VALUES( 'Measure Benefit Progress List' ),
VALUE( 'Measure Benefit Progress List'[Financial Year Search] )
= VALUE( FINANCIALYEARNEXT )
&& 'Measure Benefit Progress List'[Main ID LkUpId] = MBM_Id
),
[CheckPoint]
)
RETURN
{ CHECKPOINTNEXT }
Nothing to change in your case since you did nothing but just extract distinct values of 'Measure Benefit Progress List'[CheckPoint].
From syntactic perspective, it equals to
VAR CHECKPOINTNEXT =
CALCULATETABLE(
VALUES( 'Measure Benefit Progress List'[CheckPoint] ),
VALUE( 'Measure Benefit Progress List'[Financial Year Search] )
= VALUE( FINANCIALYEARNEXT ),
'Measure Benefit Progress List'[Main ID LkUpId] = MBM_Id
)
Expertise = List.Accumulate( {Days as from Today}, {Skills and Knowledge}, (Current, Everyday) => Current & Day.LearnAndPractise(Everyday) ) |