Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register 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) ) |
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
16 | |
13 | |
12 | |
11 | |
11 |
User | Count |
---|---|
19 | |
14 | |
14 | |
11 | |
9 |