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

Calling all Data Engineers! Fabric Data Engineer (Exam DP-700) live sessions are back! Starting October 16th. Sign up.

Reply
garythomannCoGC
Impactful Individual
Impactful Individual

How to rewrite summarize with addcolumns for my scenario

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 }

 

 

1 ACCEPTED SOLUTION
ThxAlot
Super User
Super User

(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)


)



View solution in original post

2 REPLIES 2
garythomannCoGC
Impactful Individual
Impactful Individual

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.

ThxAlot
Super User
Super User

(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)


)



Helpful resources

Announcements
FabCon Global Hackathon Carousel

FabCon Global Hackathon

Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes!

October Power BI Update Carousel

Power BI Monthly Update - October 2025

Check out the October 2025 Power BI update to learn about new features.

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.